One column with multiple foreign keys situation
First of all sorry for my bad english in advance. And my 5-minute paint
skills :D
Here's the deal : this is my first ever database project and i am afraid
my solution to this problem isnt quite the best. The database keeps track
of different "types" of cooperators. Those types are companies,
organisations, employed workers, other "persons" ... All of those have
entierly different sets of information, but they all have one in common -
contact information. I decided to let user enter what kind of contact
information he wants to add to any of the cooperator, whether its e-mail,
phone, URL, Fax and so on ... So i created "Contacts" table in which all
the contact data for all the cooperators will be put, regardless of what
type the cooperator is it.
The TablesList is the table that contains the list of cooperator types
(Copmanies, Organisations, Workers)
Each row in the Contacts table must contain "TableID" number which
identifies what type of the cooperator is it
(Company/Worker/Organisation...), and must contain "RowID" which
identifies what exact company/worker/organisation the contact is about.
The problem that exists it that Contacts table contains foreign keys from
3 other tables in 1 column, which cannot be good. I could remove the
relationships and just fill the column with thos ID's without the DBMS
knowing about the constrains, but that just doesnt look like a good
solution to me, so now i doubt this idea is any good.
What do you suggest ?
Keep in mind that in future there may be some more types of cooperators
added if needed (like temp/contract workers, agencies) and Contacts table
should be designed to support them too
Thanks in advance !
Btw im using SQL CE and C#
heres the sketch of whats going on :
SKETCH-CLICK
 
No comments:
Post a Comment