Database Design - Relationships

[lionelbrown]

Hello!

I'm designing a database that contains a Homesite table (stores info about a new home) which will have RealitorID, InspectorID, ServiceRepID, etc. as attributes.
These are all foreign keys to a contact person. I have a table (Contacts) which contains info about a contact (name, phone, etc) and a related table ContactRole table which stores 1 or more roles for the Contact Person (Salesperson, Customer Service, etc).
My question is this:
Should I lump all the contacts into one table and have the Homesite table relate to the Contacts table multiple times (for example: RealitorID, InspectorID, ServiceRepID would all be foreign keys to the Contact table (which has ContactID as the primary key)? Or should I split the Realitors, Inspectors, etc. into their own tables?

I realize I would have to join the Contacts table multiple times in queries to pull all the info for the Homesites table ... would this cause major issues??

If anyone can give me suggestions on the best approach (those listed above, or a better way) I would GREATLY appreciate it!

Thanks in advance!

 

[Delphin]

From my experience if there is any possible way that you may be repeating an ID then it is best to keep them in separate tables. This makes it much easier for data retrieval and allows you to mix and match for a person. It also saves memory and space on retrieval as you are only retrieveing what you need.