SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Please help with my data data model design.


Please help with my data data model design.

Author
Message
marksquall
marksquall
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 206
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
Attachments
data_model.jpg (87 views, 124.00 KB)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100125 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
marksquall
marksquall
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 206
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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100125 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
marksquall
marksquall
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 206
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. w00t

Very warm regards,

Mark Squall

________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100125 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Dev
Dev
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9254 Visits: 1602
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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)SSC Guru (100K reputation)

Group: General Forum Members
Points: 100125 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97167 Visits: 38988
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?

Cool
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)
danaanderson
danaanderson
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 189
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search