Table Design
Hello,
I have a table called School. Then I have a table Called SchoolContacts. They are joined by a SchoolID.
Then, I have a table called Hosptial, as well as HosptialContacts. They are joined by HospitalID.
SchoolContacts & HospitalContacts have the same fields except SchoolID and HospitalID.
Is seperating these two contact tables the best table design, or is there a better design or other options to keep all contacts in one table, and possibly eliminating some redudancy.
I am using linq to sql as well as sql server.
Thanks.
I have a table called School. Then I have a table Called SchoolContacts. They are joined by a SchoolID.
Then, I have a table called Hosptial, as well as HosptialContacts. They are joined by HospitalID.
SchoolContacts & HospitalContacts have the same fields except SchoolID and HospitalID.
Is seperating these two contact tables the best table design, or is there a better design or other options to keep all contacts in one table, and possibly eliminating some redudancy.
I am using linq to sql as well as sql server.
Thanks.
It depends on the queries if you keep them separate table and your queries does not access both contacts then its okay to separate them as they increase performance, because when joining tables only few records need to be compared.
If your queries are other way, then better way i feel is to keep all contact in a single table, in this case you should have a master table for ContactType where you have master records like 1- School, 2-Hospital, 3-Company etc, and this ContactTypeID should be a foreign key to your ContactTable.
If your queries are other way, then better way i feel is to keep all contact in a single table, in this case you should have a master table for ContactType where you have master records like 1- School, 2-Hospital, 3-Company etc, and this ContactTypeID should be a foreign key to your ContactTable.
ASKER
There is different data in the contact table, only column names are similar
>> SchoolContacts & HospitalContacts have the same fields except SchoolID and HospitalID. <<
Technically what you have looks like a super-type with sub-types:
Contact = super-type
which is either:
1) School Contact
or 2) Hospital Contact
If you consider them the same basic "thing"/entity, thencontact data should definitely be stored in a single table with both types in it.
The real issue then is whether you need a third table to represent the relationship between Contacts and their "source" (School or Hospital). That is, is a "Contact" generic, with no type assigned to it in the contact table, and it is then associated with either School or Hospital or both by a second table? Or does the Contact table contain a type, and is fixed to that type only?
Personally, I would 100% make a "Contact" row generic, and have a separate relationship table. To me, this design is the most resilient to changing conditions, and the easiest to use in practical terms.
If you want more info on that approach, just let me know.
Technically what you have looks like a super-type with sub-types:
Contact = super-type
which is either:
1) School Contact
or 2) Hospital Contact
If you consider them the same basic "thing"/entity, thencontact data should definitely be stored in a single table with both types in it.
The real issue then is whether you need a third table to represent the relationship between Contacts and their "source" (School or Hospital). That is, is a "Contact" generic, with no type assigned to it in the contact table, and it is then associated with either School or Hospital or both by a second table? Or does the Contact table contain a type, and is fixed to that type only?
Personally, I would 100% make a "Contact" row generic, and have a separate relationship table. To me, this design is the most resilient to changing conditions, and the easiest to use in practical terms.
If you want more info on that approach, just let me know.
ASKER
Yes please explain the relationship table.
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> This is properly normalised then as well <<
No, it really isn't.
You've basically got a repeating group in the School and Hospital IDs -- they could just as easily be named "Relationship1" and "Relationship2".
No, it really isn't.
You've basically got a repeating group in the School and Hospital IDs -- they could just as easily be named "Relationship1" and "Relationship2".
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not sure I follow. What are you trying to retrieve?
SELECT h.*, ri.Name FROM Table1 h
INNER JOIN RelationShipInfo ri ON ri.RID = h.RelationShipType
WHERE ri.ID = 1 --if you want to retrieve all for school
Does this answer your question?
SELECT h.*, ri.Name FROM Table1 h
INNER JOIN RelationShipInfo ri ON ri.RID = h.RelationShipType
WHERE ri.ID = 1 --if you want to retrieve all for school
Does this answer your question?
ASKER
Thanks everyone!
ASKER
ScottPletcher,
For inserting into the contact relationship, the contact table would need to be inserted first and then the contact relationship table populate correct?
I'm just trying to think of this too in the terms of using my current linq to sql or future entity framework.
For example, it would be
hospital -> contact relation -> contact
on insert, what would be the order? I insert the contact table first, and then the contact relation? but wouldn't that cause problems because inserting in contact would require a record to be in contact relation first? wouldn't I get referential integrity errors?
If I just used a supertype column in a contact table and didnt have a relationship table, then my enitity framework would retrieve all rows joining for example hospital.hospitalid = contact.relationid, and I would have to always specify a where cause, where type = H. But that where would be after the fact, it would retrieve all records for the join and that relationid could be a hospitalid or schoolid.
I have a same table structure for a files table, so I have schoolfiles, hospitalfiles, etc...I would like to simplify the design for easy of maintenance.
For inserting into the contact relationship, the contact table would need to be inserted first and then the contact relationship table populate correct?
I'm just trying to think of this too in the terms of using my current linq to sql or future entity framework.
For example, it would be
hospital -> contact relation -> contact
on insert, what would be the order? I insert the contact table first, and then the contact relation? but wouldn't that cause problems because inserting in contact would require a record to be in contact relation first? wouldn't I get referential integrity errors?
If I just used a supertype column in a contact table and didnt have a relationship table, then my enitity framework would retrieve all rows joining for example hospital.hospitalid = contact.relationid, and I would have to always specify a where cause, where type = H. But that where would be after the fact, it would retrieve all records for the join and that relationid could be a hospitalid or schoolid.
I have a same table structure for a files table, so I have schoolfiles, hospitalfiles, etc...I would like to simplify the design for easy of maintenance.
>> I insert the contact table first, and then the contact relation? <<
Yes.
>> but wouldn't that cause problems because inserting in contact would require a record to be in contact relation first? <<
NO. Contact is an independent table, and contains ONLY contact info: name, address, etc., of the contact.
The Contact relation table contains ALL relationship info, and ONLY such info.
[But, as you noted, someone MUST be in the Contact table first in order to be added to Contact_Relationship, beause ContactID is a required FK in the C_R table.]
I gave some sample columns demonstrating this in my previous post; to reiterate and to clarify:
Contact (
ContactID,
name,
phone_number,
last_updated,
last_confirmed,
...
) --note that NO relationship data goes here, following normalization rules
Contact_Relationship (
RelationType, --['H'/'S' or 1=H/2=S ...]
RelationID,
ContactID,
?Type, --type of contact, such as "Emergency", "Insurance Qs", etc.
?Sequence, --call seq 1 first, if no response contact seq 2, etc.
last_updated,
last_confirmed,
...
) --note that ONLY relationship data goes here, following normalization rules
In summary:
EVERY column in the Contact table should be CONTACT DATA **ONLY**.
EVERY column in the C_R table should be CONTACT_RELATIONSHIP DATA **ONLY**.
Allowing data unrelated to the key of a table into that table is a common design error [which, in technical terms, violates 3rd normal form]. If you do that, it will cause you a LOT of trouble later ... so just don't do it :-) .
Yes.
>> but wouldn't that cause problems because inserting in contact would require a record to be in contact relation first? <<
NO. Contact is an independent table, and contains ONLY contact info: name, address, etc., of the contact.
The Contact relation table contains ALL relationship info, and ONLY such info.
[But, as you noted, someone MUST be in the Contact table first in order to be added to Contact_Relationship, beause ContactID is a required FK in the C_R table.]
I gave some sample columns demonstrating this in my previous post; to reiterate and to clarify:
Contact (
ContactID,
name,
phone_number,
last_updated,
last_confirmed,
...
) --note that NO relationship data goes here, following normalization rules
Contact_Relationship (
RelationType, --['H'/'S' or 1=H/2=S ...]
RelationID,
ContactID,
?Type, --type of contact, such as "Emergency", "Insurance Qs", etc.
?Sequence, --call seq 1 first, if no response contact seq 2, etc.
last_updated,
last_confirmed,
...
) --note that ONLY relationship data goes here, following normalization rules
In summary:
EVERY column in the Contact table should be CONTACT DATA **ONLY**.
EVERY column in the C_R table should be CONTACT_RELATIONSHIP DATA **ONLY**.
Allowing data unrelated to the key of a table into that table is a common design error [which, in technical terms, violates 3rd normal form]. If you do that, it will cause you a LOT of trouble later ... so just don't do it :-) .
>>
hospital -> contact relation -> contact
for example hospital.hospitalid = contact.relationid, and I would have to always specify a where cause, where type = H.
<<
In general, yes, I think that's easiest. But the "H" would not be hard-coded but stored in the hospital table, so that the join is "natural" based on columns, not hard-coded values.
[You could, instead, make the relationid a unique value, regardless of whether it was hospital or school or anything else, but you then have to have a separate mechanism for assigning unique ids, and that can be a pain to manage.]
Hospital (
Id,
RelationType char(1) DEFAULT 'H' CHECK(RelationType = 'H'),
name,
...,
FOREIGN KEY ( RelationType, Id ) REFERENCES Contact_Relationship ( RelationType, RelationID ),
...
)
SELECT ...
FROM dbo.Hospital h
INNER JOIN dbo.Contact_Relationship cr ON
cr.RelationType = h.RelationType AND
cr.RelationID = h.HospitalID
...
Note that if you ever decide to combine two (or more) contact types in the same table, all the relationships still work perfectly: some rows could have 'H', others could have 'S', and the join would still be good.
hospital -> contact relation -> contact
for example hospital.hospitalid = contact.relationid, and I would have to always specify a where cause, where type = H.
<<
In general, yes, I think that's easiest. But the "H" would not be hard-coded but stored in the hospital table, so that the join is "natural" based on columns, not hard-coded values.
[You could, instead, make the relationid a unique value, regardless of whether it was hospital or school or anything else, but you then have to have a separate mechanism for assigning unique ids, and that can be a pain to manage.]
Hospital (
Id,
RelationType char(1) DEFAULT 'H' CHECK(RelationType = 'H'),
name,
...,
FOREIGN KEY ( RelationType, Id ) REFERENCES Contact_Relationship ( RelationType, RelationID ),
...
)
SELECT ...
FROM dbo.Hospital h
INNER JOIN dbo.Contact_Relationship cr ON
cr.RelationType = h.RelationType AND
cr.RelationID = h.HospitalID
...
Note that if you ever decide to combine two (or more) contact types in the same table, all the relationships still work perfectly: some rows could have 'H', others could have 'S', and the join would still be good.
ASKER
Thanks Scott for the explaination. I was missing the RelationType in the hosptial or school tables that would be the join to the c_r table. That makes sense now, and I can see that working well with entity framework, etc, as the c_r would be a natural entity join.
I'll need to convert my existing tables, but i think the design you proposed is the best for maintanability...
Thanks again...
I'll need to convert my existing tables, but i think the design you proposed is the best for maintanability...
Thanks again...
ASKER
See my tables and error below. I have primary key on hosptial table of HospitalID, RelationTypeID and for ContactRelationship table I have a primary key of RelationTypeID, RelationID, and ContactID.
When I goto add a foreign key on the hospital table, it gives me the error.
2013-02-09-111416.gif
2013-02-09-111354.gif
2013-02-09-111402.gif
When I goto add a foreign key on the hospital table, it gives me the error.
2013-02-09-111416.gif
2013-02-09-111354.gif
2013-02-09-111402.gif
ASKER
my realtiontypeid is 1=hosptial, 2=school, etc, and I have those defaulted.
The index on Hospital must include the type.
You can put the id first in the relationship if that's easier for existing data:
(HospitalID, RelationType)
But, yes, an exactly matching index must be present to use a FK.
You can put the id first in the relationship if that's easier for existing data:
(HospitalID, RelationType)
But, yes, an exactly matching index must be present to use a FK.
http://en.wikipedia.org/wiki/Database_normalization
http://databases.about.com/od/specificproducts/a/normalization.htm
There are 4 main stages of normalization that you can take your database design through, there are more but not generally used in practice. I would use a guide to take you through the process.
http://www.w3schools.in/mysql/database-normalization/