Child Table With Two Parent Tables

[Neil]

I have a database with two tables of entities. Both tables will have a
child address table, as follows:

Table1:
PK: T1_ID

Table1_Addresses:
PK: T1Ad_ID
FK: T1_ID

Table 2:
PK: T2_ID

Table2_Addresses:
PK: T2Ad_ID
FK: T2_ID

The client I'm doing it for, though, would like one address table,
shared by both tables. That would require something like this:

Addresses:
PK: Ad_ID
FK: T1_ID
FK: T2_ID

with Table1 and Table2 joined, alternatively, to the two FKs. That
configuration seems non-normalized to me, though it seems it would work.

I'd be interested in hearing people's opinions about that approach, and
whether it's a good way to go.

Thanks!

Neil

 

[Access Developer]

Are there more than one Address per record in Table 1 and Table 2? If not,
put the FK in those tables to point to the appropriate address.

If you can have multiple addresses per record; and an address can apply to
multiple records, then you have a many-to-many relationship, represented in
Access by what we call a 'junction' or 'intersection' table, whose records
(usually) have nothing but a key to each of the two related tables. In your
case, you'd need two keys to T1 and T2 and one to the Address table.

 

[Neil]

Hi, Larry. Yes, there would be multiple addresses per parent. But each
address would associated with one table only. So it wouldn't be
many-to-many.

Basically, it's two one-to-many address tables that the client would
like to combine into one, for the sake of not having two identical
tables to maintain. If this approach were used, and there were two FKs
in the Address table, then one FK or the other would be blank within
each record. Each address record would only be associated with one
parent table.

Like I said, I've never done that before. I always just create one child
table for each parent table in a one-to-many relationship, even if two
child tables had the same structure. But I thought I'd run it by here to
see if maybe this was an accepted approach that perhaps I just wasn't
aware of.

Thanks!

 

[Access Developer]

You're welcome. I see no reason it won't work for you with two foreign keys
in the Address records... depending on the details of how you use it, you
might be able to get by with just one foreign key that would apply to Tables
1 and 2, but I can imagine some situations where two would be necessary.

 

[Neil]

You're welcome. I see no reason it won't work for you with two foreign keys
in the Address records... depending on the details of how you use it, you
might be able to get by with just one foreign key that would apply to Tables
1 and 2, but I can imagine some situations where two would be necessary.

Well, having two FKs differentiates which table it belongs to. If I had
one FK, I'd have to add a field to indicate which parent table the
address belongs to, and include that in any queries. Seems messier to me.

My main reason for posting this was more of what the "appropriate"
approach would be. I'm sure both approaches would work. But which is
seen as the "preferred" approach, is what I mean.

Again, two main tables, each with their own set of addresses. No
addresses would apply to more than one record. A main table record may
have multiple addresses, though.

So:

1) Have two identical address child tables, each with a single FK, and
have a one-to-many relationship from each main table to each of the
child tables. Or:

2) Have one address child table with two FKs, each FK used by one of the
main tables. Each address child record would have one FK completed and
one FK Null. The two main tables would "share" the single child address
table.

I'm looking to see which of the two is viewed as a more normalized
approach. Which would you use, for example?

Thanks.