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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101059 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5665 Visits: 860
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
Steve Thompson
Steve Thompson
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 2185
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).
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 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...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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64940 Visits: 17979
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.

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)
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5665 Visits: 860
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
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
To danaanderson:

I confess I am doing normalization to the death. Crying
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. Laugh


I will keep on studying, experimenting and asking questions. :-D


Warm regards,

Mark Squall

________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
patrickmcginnis59
patrickmcginnis59
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1615 Visits: 2333
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.
Steve Thompson
Steve Thompson
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
Points: 814 Visits: 2185
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).
patrickmcginnis59
patrickmcginnis59
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1615 Visits: 2333
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.
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