-1

Is it possible/or good practice to have one table like an address book that is related to different entities e.g: People, Companies, etc I am thinking of using a compsite FK (typeid = entity type id e.g: 1 for people, 2 for companies etc, while the other column refers to the pk of the entity's table).

The other alternative I have is to use a junction table between the entities and address book table.

I am using VS 2005 C#, and sql server 2008

TechComp
  • 1
  • 2
  • This is a generic database design question. – gbn Dec 12 '14 at 08:52
  • The VS C# part is needed for binding this design. – TechComp Dec 12 '14 at 14:02
  • My C# delima is: I needed in my UI to bind to one datasource (Address) and have it display all the address, if it comes to People's address it should that by choosing a radio buttun, the same for Companies – TechComp Dec 12 '14 at 15:19

2 Answers2

0

Assumption: your entities are all stored in one table with a type discriminator

Yes, a foreign key can have multiple columns.

An address entry only needs to know the PK of the parent entity
It does not need to know the type of entity: entityType is an attribute of entity not address

Junction table or straight FK? It depends on your relationships:

  1. Can one address be shared by more than one entity?

  2. Can one entity have more then one address?

    • Yes to both: junction table
    • Yes to Q1 only: address PK is an attribute of entity. Entity is the child table in the FK
    • Yes to Q2 only: entity PK is an attribute of address. Address is the child table in the FK

And the case I would not expect:

  • No to both: 1:1 relationship or you need only one table, address is an attribute of entity
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Well, thanks for your time to respond, but, each entity has different schema, thus, I believe the address table should know about it. – TechComp Dec 12 '14 at 13:34
  • Right, so give us all the information we need then. Anyway you can't have an FK to multiple parents but feel free to try. So you need a supertype-subtype solution http://stackoverflow.com/a/7527933/27535 and http://stackoverflow.com/a/5451528/27535 – gbn Dec 12 '14 at 13:37
  • I read the two threads, looks like the second one is similar to what I want to do. Person would be(id, firstN, lastN, sex, dob), Companies (id, name, contactid - fk to person), now the address (id, entityid, entityTypeID, city, province). If I understood correctly from the second thread in the link you provided, this would be possible and has no implications on my application. Note the question is also related to c# not only db.thx. – TechComp Dec 12 '14 at 13:58
  • This is a database design problem, not a c# one – gbn Dec 12 '14 at 13:59
  • I just came to this link SQL by Design: Supertypes and Subtypes[link](http://sqlmag.com/t-sql/sql-design-supertypes-and-subtypes), it looks like it suggests gbn's answer, and to some extents the other part of my problem: UI binding. Any more suggestions? Does this approach have serious implications? – TechComp Dec 12 '14 at 15:34
0

I see no need for a type and an entity_id that refers to one table or the other. This is a complicated approach (two tables using the same ID pool and no foreign key to a table possible) that might cause problems later.

Your problem seems so simple that no tricks should be needed here.

You have companies, persons and addresses. If each person and each company has one address, you have the address id in the companies and persons tables. If a person can have more than one address you need a bridge table. Same for more than one address per company. That's all.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Actually i mentioned that this is my second choice, it looks you advocate this approach since it complicates things later. But I thought about the first approach cause I needed in my UI to bind to one datasource (Address) and have it display all the address, if it comes to People's address it should that by choosing a radio buttun, the same for Companies. If the approach does not solve my problem without complications I would go for the second ( bridge, junction table) unless someone suggests another solution. – TechComp Dec 12 '14 at 15:10
  • So far I haven't tried this in C#, but this cannot be a problem, because the information is there. You don't link to a table, but to a query. And you can simply write a query telling you whether an address belongs to a person or a company and then filter accordingly. Like so: `select a.*, case when exists (select * from companies c where c.address_id = a.id) then 1 else 0 end as is_company_address from addresses`. – Thorsten Kettner Dec 12 '14 at 15:42
  • This could be OK if one company per se has one address, but we need one company with many addresses, and the same for persons. – TechComp Feb 06 '15 at 18:39