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!

 

[svanels]

I would store them in different tables. Reasons:

Readability, if another person have to maintain or modify the database it is easier to get the whole picture of the database structure.

Coding, with different funcionalities in the same table, you must instruct your application to do special processing if you need only a list of inspectors.

Scalability, if you need to upscale or transport to another database it is easier S. van Els
SAvanEls@cq-link.sr

 

[lionelbrown]

svanels, thanks for the reply!

I agree with each of the reasons you've listed. So if you have a chance I would like your opinion on this design (let me know if this is what you had in mind):

A Person table (super-class) with all the attributes relating to an abstact person (name, phone, etc.), and a table for each of the various roles (Inspectors, etc sub-classes that contain the attributes relevant to that particular role) with a foreign key to Person.

My goal is to manage all contacts from the same interface. I suppose once a contact person is selected I could query each of the sub-class tables for the ContactID and fill a combo or list box with the roles the person has, and then fill a grid with the attributes for that role once it has been selected from the combo box. Or I could require selection of a role first, then select a contact, etc.

I should really take the ER model and "turn the crank" to convert it to a relational schema, but I'm working with an existing DB and I'm trying to balance a good design against going back and reworking existing interfaces. I know the "official" answer is start with a good design at all costs, but of course in the real world you have to deal with time and cost constraints.

Once again, thanks for your help!

 

[jhowley58]

I do exactly that.. upwards of 10k contacts(persons) in a contacts table(your generic person table) with foreign keys to the associated companies. It's a bit more complicated than that because the companies are members of company type tables. Maybe 7k+ companies.

John Howley