Please help with my data data model design.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Relational Theory » Please help with my data data model design.

23 posts, Page 1 of 3

1 2 3 »»»

Please help with my data data model design.

Rate Topic

Display Mode

Topic Options

Author

Message

marksquall

Posted Tuesday, April 24, 2012 2:40 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 7:06 AM
Points: 23, Visits: 155

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 (38 views, 124.51 KB)

Post #1288724

Grant Fritchey

Posted Tuesday, April 24, 2012 4:54 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

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

marksquall

Posted Tuesday, April 24, 2012 5:46 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 7:06 AM
Points: 23, Visits: 155

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

Grant Fritchey

Posted Tuesday, April 24, 2012 6:01 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

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

marksquall

Posted Tuesday, April 24, 2012 8:19 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 17, 2013 7:06 AM
Points: 23, Visits: 155

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

Post #1289514

Grant Fritchey

Posted Wednesday, April 25, 2012 4:22 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

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

Dev

Posted Wednesday, April 25, 2012 10:50 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:05 PM
Points: 2,013, Visits: 1,566

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~

Post #1290108

Grant Fritchey

Posted Wednesday, April 25, 2012 10:57 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 13,436, Visits: 25,281

Dev (4/25/2012)

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

Lynn Pettis

Posted Wednesday, April 25, 2012 10:58 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:17 PM
Points: 21,832, Visits: 27,855

Grant Fritchey (4/25/2012)

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)

Post #1290115

danaanderson

Posted Thursday, April 26, 2012 6:42 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 07, 2013 9:41 PM
Points: 18, Visits: 58

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. Smile

Dana

Post #1291195

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions