April 24, 2012 at 2:40 am
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 [font="Courier New"]Person[/font], in which [font="Courier New"]Student[/font] table is "connected", but I will add [font="Courier New"]Employee[/font] and [font="Courier New"]Faculty[/font] 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 [font="Courier New"]Address[/font] and [font="Courier New"]Phone[/font] 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
April 24, 2012 at 4:54 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2012 at 5:46 am
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 [font="Courier New"]PERSONAL_CONTACTS[/font].
I designed a form in VB .NET, in which a user can fill a text field of three (3) kinds of address: [font="Courier New"]Permanent[/font], [font="Courier New"]Mailing[/font], and [font="Courier New"]Email[/font]. But the user may leave it blank. And another two (2) fields for contact numbers, [font="Courier New"]Landline[/font] and [font="Courier New"]Mobile[/font] respectively, again, the user may leave it blank.
The most important data entry is the [font="Courier New"]PERSON_ID[/font] (it has a property of [font="Courier New"]IDENTITY(1,1) PRIMARY KEY CLUSTERED[/font] in MSSQL Server 2008),[font="Courier New"]STUDENT_NUMBER[/font] (auto-generated ID by our VB code), [font="Courier New"]FIRST_NAME[/font], [font="Courier New"]MIDDLE_NAME[/font], and [font="Courier New"]LAST_NAME[/font].
This is my plan (initial) of creating [font="Courier New"]PERSONAL_CONTACT[/font] table. These are the column names:
[font="Courier New"]PERSON_ID
PERMANENT_ADDRESS
MAILING_ADDRESS
EMAIL_ADDRESS
LANDLINE
MOBILE[/font]
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
April 24, 2012 at 6:01 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2012 at 8:19 pm
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?
[font="Courier New"]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?)[/font]
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
April 25, 2012 at 4:22 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2012 at 10:50 am
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.
April 25, 2012 at 10:57 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2012 at 10:58 am
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?
April 26, 2012 at 6:42 pm
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
April 27, 2012 at 4:38 am
danaanderson (4/26/2012)
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
Possibly true. I've just been around the block a time or two. I've had the problem of multiple entries of a single address, each one slightly different so that searches & reports are all skewed. Then you have to go through a clean up process... blech. So, yeah, I might design as if I were avoiding problems of this type which will only usually be a problem as the system scales. If you never have but a few hundred records, getting that design right doesn't matter that much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2012 at 4:47 am
another perspective on this - your primary keys and foreign keys are the same - this doesn't sit well with me
my version of the students table would be (i'm assuming int columns just for the sake of syntax)
create table students
(
student_no int not null Primary key
,person_id int not null foreign key references person(person_id)
)
surely no student will have deplicate student numbers? therefore it is (IMHO) a better choice for primary key as it is the unique identifier for the entity, rather than an extension of another table
just an opinion though since you don't normally expect to create foreign keys from primary to primary
MVDBA
April 27, 2012 at 7:47 am
I'm curious as to why an attribute such as Student is getting it's own table. Is there more Student-related data that needs to be tracked?
I'd be tempted to have a PersonType lookup table (Values: Student | Employee | Faculty); and a PersonType_id FK in the Person table. If people can have more than one type, then you can make the relationship Many-to-Many.
Not sure if this would be an improvement over what you already have, but it might scale better, especially if you plan on adding more person types in the future (if you had 10 roles a person could play, instead of 10 tables you'd have 10 rows in a lookup table).
April 27, 2012 at 7:48 am
Dear all,
Thank you for your ideas guys, I am learning one step at a time to database stuffs.
For the meantime...I just used this table named [font="Courier New"]ADDRESS[/font], the columns are:
[font="Courier New"]person_id (int)
permanent (nvarchar(50))
mailing (nvarchar(50))
email (nvarchar(50))[/font]
I allowed [font="Courier New"]null[/font] for the three addresses. This [font="Courier New"]person_id[/font] is a foreign key to the [font="Courier New"]PERSON[/font] table's [font="Courier New"]person_id[/font]. Since the data entry in my VB .NET Form is of only three fields for the addresses, then maybe the simple program I am creating should not take into consideration (for the moment) if the student has many more than three emails and/or addresses, or if he/she has five contact numbers using five iPhones...:w00t:
I think what is wrong with our first data model (as attached to the opening of this thread) when we tried to "simulate" it in a piece of paper, it looks like a student can only have one entry for address and one entry for contact number, but to my VB .NET Form, the student-add-a-new-record-form have at least three (3) entries for addresses and at two (2) entries for contact numbers...
Well, back to the drawing board, I guess... :satisfied:
Your ideas and suggestions will be of great help in my succeeding database design in the future.
God bless you all.
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
April 27, 2012 at 9:14 am
With this type of schema you have create a 1:1 relationship from Person to Address. You did not gain any of the advantages of normalization with this. You will 1 and only 1 row in the Address table for each Person. I would suggest to have an Address table with Person_ID, Addr1, Addr2, City, ST (and what ever fields for the physical address you need) and finally an AddressType column. This last column would tell you if this is the permanent, mailing, parents etc. This table should not have email. That should be in another table (PersonEmail or similar). This would have maybe 3 columns. Person_ID, EmailAddress, IsPrimary. This would allow to have as many email addresses as they want/need.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply