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 Friday, April 27, 2012 4:38 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 15,661, Visits: 28,047
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
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 #1291377
Posted Friday, April 27, 2012 4:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,285, Visits: 781
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
Post #1291382
Posted Friday, April 27, 2012 7:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 290, Visits: 1,669
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).

Post #1291543
Posted Friday, April 27, 2012 7:48 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 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 ADDRESS, the columns are:

person_id (int)
permanent (nvarchar(50))
mailing (nvarchar(50))
email (nvarchar(50))


I allowed null for the three addresses. This person_id is a foreign key to the PERSON table's person_id. 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...

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

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
Post #1291545
Posted Friday, April 27, 2012 9:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1291642
Posted Friday, April 27, 2012 9:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,285, Visits: 781
i often find it beneficial to start with a number of sentences describing your objects before you start designing tables and relationships - then you can check off that your schema matches your requirements

for example

There are many people
many people can be students
many people can be employees
a person may have many addresses
a person may have many telephone numbers
can a person be a student and an employee at the same time?

as simple as this is it really helps to design a database


MVDBA
Post #1291656
Posted Friday, April 27, 2012 6:57 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
To danaanderson:

I confess I am doing normalization to the death.
I know I am a beginner and I think I need to learn too about normalization as well.
I have a little background in normalization and I thought that on my data model, I had achieved this, but as usual, professional people like you all have seen so many flaws in my data model. I need to read more.


To Sean Lange:

Yes Sir, I noticed the 1:1 relationship when I tried to simulate a sample data in a scratch paper. What I really want is 1-to-many relationship (i.e., a student can/may have a permanent, mailing and/or an e-mail address). Thank you for the suggestion, it seems much better from my former ADDRESS table, I will do your suggestion Sir.


To michael vessey:

Thank you Sir. I guess/realize that it will be much better to do your "sentence describing objects" first, before creating tables on SQL Server. I was just really excited to do stuffs in MS SQL Server. I was a total beginner to SQL Server, now I know how to use the program little by little, but I know to myself that I have still a very long, long way to go.


I will keep on studying, experimenting and asking questions.


Warm regards,

Mark Squall


________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Post #1291931
Posted Thursday, May 3, 2012 10:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
Steve Thompson-454462 (4/27/2012)
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 would have a person table and a student table, this way you could have rows for people who aren't students.
Post #1294796
Posted Thursday, May 3, 2012 10:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 290, Visits: 1,669
patrickmcginnis59 (5/3/2012)
Steve Thompson-454462 (4/27/2012)
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 would have a person table and a student table, this way you could have rows for people who aren't students.


In my full post I recommend having a Person table (which would contain all People) and a PersonType lookup table that would contain the "roles" a person could have - there could be a Many-to-Many relationship between the tables if a Person is allowed to have multiple roles.

So, in that design, you could very easily store People who are not Students.

My issue with tracking the attribute "student" through a dedicated table was one of scalability: as you add roles you'd have to add the equivalent number of tables; in other words, adding a new role requires a schema change. In the lookup table design, adding a new role simply involves a data change (adding a row to the lookup table).
Post #1294802
Posted Thursday, May 3, 2012 12:03 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
Steve Thompson-454462 (5/3/2012)
patrickmcginnis59 (5/3/2012)
Steve Thompson-454462 (4/27/2012)
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 would have a person table and a student table, this way you could have rows for people who aren't students.


In my full post I recommend having a Person table (which would contain all People) and a PersonType lookup table that would contain the "roles" a person could have - there could be a Many-to-Many relationship between the tables if a Person is allowed to have multiple roles.

So, in that design, you could very easily store People who are not Students.

My issue with tracking the attribute "student" through a dedicated table was one of scalability: as you add roles you'd have to add the equivalent number of tables; in other words, adding a new role requires a schema change. In the lookup table design, adding a new role simply involves a data change (adding a row to the lookup table).


I think thats fine if you are only storing the role and thats as far as you go with it, so I'm ok with this. If you are going to store additional info regarding the persons role as a student then the student table makes sense.
Post #1294859
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse