April 27, 2012 at 9:42 am
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
April 27, 2012 at 6:58 pm
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 [font="Courier New"]ADDRESS[/font] 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. 😀
Warm regards,
Mark Squall
________________________________
"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
May 3, 2012 at 10:39 am
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.
May 3, 2012 at 10:49 am
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).
May 3, 2012 at 12:03 pm
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.
May 3, 2012 at 12:46 pm
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.
Yup, I agree (my original post asks whether they are planning on tracking extra student-specific data).
If you need to track data that is applicable across all roles (e.g. a RoleStarted/RoleEnded date) then those columns could be added to the many-to-many link table without adding a specific table for each role.
However, if in your business model you are tracking attributes specific to students, professors, employees, etc., then a table to organize these attributes makes sense.
May 4, 2012 at 2:18 am
Steve Thompson-454462 (5/3/2012)
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.
Yup, I agree (my original post asks whether they are planning on tracking extra student-specific data).
If you need to track data that is applicable across all roles (e.g. a RoleStarted/RoleEnded date) then those columns could be added to the many-to-many link table without adding a specific table for each role.
However, if in your business model you are tracking attributes specific to students, professors, employees, etc., then a table to organize these attributes makes sense.
i would say that you need to think about what will happen in thte future - while this system only really lists the names and addresses of students and employees, it's not unreasonable to say the following
students take classes , employees do not
employees have timesheets, students do not
employees get appraisals
students get grades
if any of the above ever make it into your functionality then a "role" table is (IMHO) not the best way to go - (plus with a role table you might have real issues if you are a student and an employee - as happens at some universities)
without a full understanding of the context of the model and the roadmap it's hard to say what is the correct design
MVDBA
May 5, 2012 at 2:47 am
i would say that you need to think about what will happen in thte future - while this system only really lists the names and addresses of students and employees, it's not unreasonable to say the following
students take classes , employees do not
employees have timesheets, students do not
employees get appraisals
students get grades
+1
if any of the above ever make it into your functionality then a "role" table is (IMHO) not the best way to go - (plus with a role table you might have real issues if you are a student and an employee - as happens at some universities)
He has already mention Many-to-Many relationship (thus association table) between Person & Role. However it will make the scenario overcomplicated with above questions.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply