Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Please help with my data data model design. Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2012 2:40 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 10:19 AM
Points: 28, Visits: 170
Dear members and administrator,

Hello and a pleasant day. I was given a task to create a simple data model that will implement primary key and foreign keys in tables...just came up with this sample. It has a main table called Person, in which Student table is "connected", but I will add Employee and Faculty table as well.

I just want to ask guidance if I did the correct relationship between data. I do not know If I need a separate table for Address and Phone data just connected to another table.

Attached herein is the print screen of the data model.

I hope you could help me redesign this if it is not design appropriately.

Thank you and more power.

Respectfully Yours,

Mark Squall


________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20


  Post Attachments 
data_model.jpg (61 views, 124.51 KB)
Post #1288724
Posted Tuesday, April 24, 2012 4:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 15,530, Visits: 27,911
It's basically OK. I'm not crazy about how you've set up the contacts relationship. I would have two different interim tables, one for relations between a person and an address and another for a person and a phone. I'd go ahead and add one for a person and a web site and a person and an email address. Any given person may have one or more addresses, but they may have zero or more phones, email addresses & web sites. It's a little messier to coordinate the relationships between each individual type, but it's easier to maintain and query against over time.

But, if you do leave your design as you've done it, that triangular relationship between address phone & contact, with common primary keys in those two tables is going to make querying very difficult.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1288793
Posted Tuesday, April 24, 2012 5:46 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 10:19 AM
Points: 28, Visits: 170
Sir Grant,

Thank you very much for the opinion you gave about my data design (not my original idea so the credit is not to me, just an instruction from a friend). Actually Sir, when my friend adviced me to do this, I really find it difficult how would I construct my query in my PERSONAL_CONTACTS.

I designed a form in VB .NET, in which a user can fill a text field of three (3) kinds of address: Permanent, Mailing, and Email. But the user may leave it blank. And another two (2) fields for contact numbers, Landline and Mobile respectively, again, the user may leave it blank.

The most important data entry is the PERSON_ID (it has a property of IDENTITY(1,1) PRIMARY KEY CLUSTERED in MSSQL Server 2008),STUDENT_NUMBER (auto-generated ID by our VB code), FIRST_NAME, MIDDLE_NAME, and LAST_NAME.

This is my plan (initial) of creating PERSONAL_CONTACT table. These are the column names:

PERSON_ID
PERMANENT_ADDRESS
MAILING_ADDRESS
EMAIL_ADDRESS
LANDLINE
MOBILE


Do you think Sir Grant this is an "okay" design? My friend told me that it is not advisable to have column that are almost "identical" in nature. Sir can you advice me how to create a much better table?

Thank you thank you for any ideas and advice.

Warm regards,

Mark Squall


________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Post #1288821
Posted Tuesday, April 24, 2012 6:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 15,530, Visits: 27,911
It's like I said. An address table instead of fields in the person table. That way you can store a single address one time. Then an interim table, a table that matches between the person table and the address table. It's also called a many-to-many table. It allows for a person to have more than one address. Which makes sense. You have a home address, a work address, possibly a shipping address, a billing address, etc. The interim table has in it the primary key from the person table and the primary key from the address table, which would probably be a system generated identifier, but it could be a natural key.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1288828
Posted Tuesday, April 24, 2012 8:19 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 21, 2014 10:19 AM
Points: 28, Visits: 170
Sir Grant,

Is interim table same as derived table?

Please help me Sir finalize my table. Based from your advice, are these the tables that I will create?

PERSON (column: person_id, last_name, first_name, middle_name)
STUDENT (column: person_id, student_number)
ADDRESS (column: address_id, person_id, address)

INTERIM_TABLE (column: person_id, address_id) <-- (this still confused me Sir...is this "query generated" ? or an actual table in MSSQL Server?)


Oh my golly, I forgot (or maybe I do not know) how to determine if the address is permanent, mailing, or email...silly me.

Very warm regards,

Mark Squall


________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Post #1289514
Posted Wednesday, April 25, 2012 4:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 15,530, Visits: 27,911
I would not call the table 'interim." That is a term that describes the function of the table. The table is for the many-to-many join. Many people can have many addresses. Yes, it's a table, not a derived table. It physically acts as the mapping point between a person and an address.

As to email vs. physical address, I would make two tables there. One for email. One for address. This also means two many-to-many/interim tables, one for each relationship back to the person. A person can have many email addresses.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1289707
Posted Wednesday, April 25, 2012 10:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584
I feel interim table is unnecessary here. We can simplify the relationship between person & address as one-to-many. With this we will lose the flexibility to map one address with many persons but for most of the business requirements it’s acceptable.

The address and email table should have one indicator column to identify the address or email / web contact type.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1290108
Posted Wednesday, April 25, 2012 10:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 15,530, Visits: 27,911
Dev (4/25/2012)
I feel interim table is unnecessary here. We can simplify the relationship between person & address as one-to-many. With this we will lose the flexibility to map one address with many persons but for most of the business requirements it’s acceptable.

The address and email table should have one indicator column to identify the address or email / web contact type.


That's a choice. Not the one I'd make, but I'm not saying it's wrong.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1290113
Posted Wednesday, April 25, 2012 10:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 23,047, Visits: 31,575
Grant Fritchey (4/25/2012)
I would not call the table 'interim." That is a term that describes the function of the table. The table is for the many-to-many join. Many people can have many addresses. Yes, it's a table, not a derived table. It physically acts as the mapping point between a person and an address.

As to email vs. physical address, I would make two tables there. One for email. One for address. This also means two many-to-many/interim tables, one for each relationship back to the person. A person can have many email addresses.


There are students involved here as well. What if two or more are sharing the same house or apartment? Do you want to duplicate data that doesn't need to be duplicated?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1290115
Posted Thursday, April 26, 2012 6:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 11:37 PM
Points: 52, Visits: 127
I wonder about the extra table myself. I've not made many posts here, but I felt I should put in my two cents (not that that is worth much these days). If you can be certain that there will be a fairly high percentage of students living together, then perhaps it has merit. However, if the probability is rather on the low side, the extra table would use a lot more storage space...duplicating every single person_id, plus every single address_id. Sometimes we tend to normalize a database to death. Some do it just for the sake of normalizing. I'm not accusing you of such a thing, just suggesting that it happens, probably more than some would like to admit.

Just a thought.

Dana
Post #1291195
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse