• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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.
0
michael1174
Asked:
michael1174
  • 7
  • 6
  • 2
  • +3
3 Solutions
 
Alex_WCommented:
Good database design needs to go through a process called normalization which is designed to eliminate repeating data.

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/
0
 
AnujCommented:
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.
0
 
michael1174Author Commented:
There is different data in the contact table, only column names are similar
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
>> 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.
0
 
michael1174Author Commented:
Yes please explain the relationship table.

Thanks!
0
 
Scott PletcherSenior DBACommented:
OK.

The "Contact" table just contains a ContactId and the Contact info: name, phone number(s), etc., whatever makes up your Contact info.

Then a separate table associates that Contact info with one or more SchoolIDs and/or HospitalIDs.

Something like this:

Contact (
    ContactID,
    name,
    phone_number,
    last_updated,
    last_confirmed,
    ...
)

Contact_Relationship (
    RelationType ['H'/'S'|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 this means that technically Contact info could be entered w/o a relationship.  In your current set up, though, you won't have that situation yet, and will always add a C and C_R at the same time.
0
 
esolveCommented:
The simplest is just to have one contact table. This is normalised because you only specify the contat details once.

Then contacts will have two foreign key fields one for SchoolID and one for HospitalID. These can be null so if either is not specified they are not a contact of either school or hospital.

Theres no need for contact type. This is properly normalised then as well.

See diagram attached.

SELECT * FROM Contact WHERE SchoolID = 1 --select all contacts for school 1
SELECT * FROM Contact WHERE SchoolID IS NOT NULL --select school contacts for all schools
SELECT * FROM Contact WHERE HospitalID = 3 -- select all contact for hospital 3
SELECT * FROM Contact WHERE HospitalID IS NOT NULL -- select contacts for all hospitals
DatabaseDiagram.PNG
0
 
Scott PletcherSenior DBACommented:
>> 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".
0
 
Monica PCommented:
have a table named RelationshipInfo with column as "Rid" ,"Rname"

eg :

RID Rname
 1    School
 2    Hosptial


Create table with common fields  TABLE1  in both (hosptial and School) and add RelationshipType column

Eg

Id           Name    Address    RelationshipType
10023      aaaa       India              1---------> School
20025      bbbb      India              2---------->Hospital
10333     ccccc        USA               1----------->school

When u want retrieve record use innerjoin and get the info


select * from RelationshipInfo a  inner join TABLE1   b
on a.relationshiptype = b.RID
0
 
esolveCommented:
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?
0
 
michael1174Author Commented:
Thanks everyone!
0
 
michael1174Author Commented:
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.
0
 
Scott PletcherSenior DBACommented:
>> 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 :-) .
0
 
Scott PletcherSenior DBACommented:
>>
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.
0
 
michael1174Author Commented:
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...
0
 
michael1174Author Commented:
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
0
 
michael1174Author Commented:
my realtiontypeid is 1=hosptial, 2=school, etc, and I have those defaulted.
0
 
Scott PletcherSenior DBACommented:
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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now