Friday, March 23, 2012

need design advice

we are desgning a db that will keep name and address information for all
kinds of entities: customers, employees, vendors, contacts, etc. Instead of
having N/A fields in each of the tables it seems like a good idea to have
one nameaddess table keyed by an identity int and have all of the other
tables reference this table via foreign key (either implicit or explicit).
What are the pros and cons of doing this kind of design?
Thanks,
T
Not sure what your referencing by an "N/A" field. However, if I'm
following you correctly, setting this up will depend if address can
have more than one entity (ie: one address can be a vendor and a
customer or something like that).
If not, then 2 tables should do it:
tbl_addresses
tbl_entities
the entities table will have "customers", "employees" etc. marked with
a PK (ie: "entity_id"). Then, of course, you'd have an "entity_id"
column in the addresses table that's a FK.
If an address can have more than one entity, then you can add a third
table that has three columns: the PK for the third table and 2 FK's
(one for addresses and one for entities). Here you wouldn't need the
FK in the "adresses" table.
So, let's say address #8 in the table has 1 entity and address #9 has
3, your 3rd table's data would something like:
ID ADDRESS_ID ENTITY_ID
1 8 2
2 9 2
3 9 4
4 9 5
and so on...
Of course, the "entity_id" shown above would refer to customers or
employees or whatever.
Hope that makes sense or helps in some way!
Jeremy

No comments:

Post a Comment