Solved

Table Design

Posted on 2013-01-16
18
346 Views
Last Modified: 2013-02-10
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
Comment
Question by:michael1174
  • 7
  • 6
  • 2
  • +3
18 Comments
 
LVL 7

Expert Comment

by:Alex_W
ID: 38783405
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
 
LVL 15

Expert Comment

by:Anuj
ID: 38783426
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
 
LVL 2

Author Comment

by:michael1174
ID: 38783427
There is different data in the contact table, only column names are similar
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38787843
>> 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
 
LVL 2

Author Comment

by:michael1174
ID: 38787897
Yes please explain the relationship table.

Thanks!
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
ID: 38788246
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
 
LVL 6

Assisted Solution

by:esolve
esolve earned 50 total points
ID: 38798017
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38801437
>> 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
 
LVL 10

Assisted Solution

by:Monica P
Monica P earned 150 total points
ID: 38818488
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:esolve
ID: 38818503
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
 
LVL 2

Author Closing Comment

by:michael1174
ID: 38838883
Thanks everyone!
0
 
LVL 2

Author Comment

by:michael1174
ID: 38857282
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38857358
>> 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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38857405
>>
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
 
LVL 2

Author Comment

by:michael1174
ID: 38863304
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
 
LVL 2

Author Comment

by:michael1174
ID: 38871302
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
 
LVL 2

Author Comment

by:michael1174
ID: 38871306
my realtiontypeid is 1=hosptial, 2=school, etc, and I have those defaulted.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38874380
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now