Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Unanswered: Normalization Question

    Went back and look at one of our DBs that was designed a couple of years ago that must be modified. The DB relationships work perfect but I am questioning a "one to one" relationship. I thought "one to one" relationships were frowned upon!

    Table Student (9 fields): SSN, fname, lname, mi, etc
    Table TestHistory (20 fields): SSN, grade item 1, grade item 2, grade item3, etc

    If every student is in the Table Student and every student in in Table Grades, should it be just one table? No report will be generated without combining both tables.

    Thoughts

    Peter
    Attached Thumbnails Attached Thumbnails relationship.JPG  
    Last edited by PJHAction; 05-07-10 at 15:54. Reason: (deletedGrades)
    We tend to look at Linear paths which can lead us to a path of resistance!

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by PJHAction View Post
    Went back and look at one of our DBs that was designed a couple of years ago that must be modified. The DB relationships work perfect but I am questioning a "one to one" relationship. I thought "one to one" relationships were frowned upon!

    Table Student (9 fields): SSN, fname, lname, mi, etc
    Table Grades (20 fields): SSN, grade item 1, grade item 2, grade item3, etc

    If every student is in the Table Student and every student in in Table Grades, should it be just one table? No report will be generated without combining both tables.

    Thoughts

    Peter
    Peter,

    You probobly should NOT be using a oine-to one relatkionship but a one to many because the "Table Grades" (tblISTestHistory) has serious violations of the rules of data normaization.

    Why are you relations between takes not not using the Prirmarty Key fields?

    Also SSN should NOT be uses as a primary key.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2010
    Posts
    15
    I couldnt see Table Grades, do you mean the one to one on tblSStudents and tblSTestHistory?

    To me one student can only have one test history but test history can have many tests? Have a Test_History field in tblsStudents with a one to many on tblSTestHistory... does that sound right?

  4. #4
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73

    Primary Key

    The original table used SSN for everything and was the primary key in the tables, my plan was to replace the ssn with a student ID (combination name/ssn) and then return then primary key to Stuid. Problem exists if the stuid is the primary key, then you cant change that number unless you delete the record it (right?).

    Peter
    We tend to look at Linear paths which can lead us to a path of resistance!

  5. #5
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by PJHAction View Post
    The original table used SSN for everything and was the primary key in the tables, my plan was to replace the ssn with a student ID (combination name/ssn) and then return then primary key to Stuid. Problem exists if the stuid is the primary key, then you cant change that number unless you delete the record it (right?).

    Peter
    Peter,

    I would urge you to use a system assign primary key that is not bases on any data in the record. The auto-number data type is perfect for this.


    my plan was to replace the ssn with a student ID (combination name/ssn)
    Not a good idea for a primary key and should be avoided. You will only create more headaches for yourself. Again, please study the rules for data normalization.

    If you want o create indexes on other fields, like ssn, in a table to help prevent duplicates, that is good.

    It is possible to make the SSN appear as the primary key to the users, when in reality, it is a auto number. It is common practice to never display the primary key to the users. Like in your case, the users need to never see the "student id" used by the system.
    Last edited by HiTechCoach; 05-07-10 at 16:09.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  6. #6
    Join Date
    May 2010
    Posts
    601
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There is nothing intrinsically wrong with a one-to-one relationship. The sub-type super-type modelling technique, for example, requires them. They also do not violate any normal form; indeed many relational purists would suggest that they are often required for a database to be in first normal form.

    As HiTechCoach says, the problem with test history, however, is it violates first normal form (specifically Repeating Groups). This should be a one-to-many, or likely a many-to-many relationship.

    SSNs are typically not recommended as a primary key because there are occasional collisions in the US. For the size of database you are using this is very highly unlikely to be a problem but if you one day work on a system that stores a significant proportion of the population then you will need to bear this in mind.

    HiTechCoach is giving one side to the surrogate key vs natural key debate. I will give the other. StudentID or any other natural key (such as SSN, bearing in mind the collisions issue) is perfectly fine to use in your system. Some designers chose to exclusively use surrogate keys (typically autonumbers), some exclusively natural keys, and some (like me) whatever is appropriate for the system. HiTechCoach is right that you should never expose a surrogate key to the user - it is in effect a pointer for the database's internal use only.

    Note also that surrogate keys Vs natural keys has nothing to do with normalisation. You should only look to use natural keys when creating the logical design and add surrogates when creating your physical database - second, bcnf, fourth and fifth normal form are incredibly difficult if your logical model uses surrogate keys. In case you don't know, the logical model is what you design on paper or in a modelling tool, the physical model is your actual database.

    I have seen those MS links before and IMO they suck. The two best online articles I know of covering database design and normalisation are:
    The Relational Data Model, Normalisation and effective Database Design
    That article is pretty thorough and uses the correct, technical terminology throughout.
    Fundamentals of Relational Database Design -- r937.com
    This article is a bit more accessible and uses Access for the examples.

    A decent book on the subject is Introduction to Database Systems by Chris Date who is generally recognised as the pre-eminent writer on the subject.

  8. #8
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    One of those situations where I am a one legged man in an ass kicking contest. By looking at the post it seems that the testhistory table should have a structure of Autonum, Stuid, TestName; TestScore to capture all the tests on a one-to-many relationship
    We tend to look at Linear paths which can lead us to a path of resistance!

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes. Maybe other stuff like date taken.
    You might also want a Test table to provide the test names. This is only necessary from a normalisation perspective if there are things you need to record about tests (for example, examining body, maximum score - that sort of thing). Another reason you might want this, from another perspective, is as a look up table for the UI to restrict the allowable values for the TestName column. This is why I said this requires either a one-to-many or many-to-many set up: including a test table makes this many-to-many.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I am chuckling at you calling this an ass kicking contest - I wasn't flaming HiTechCoach. (S)he has posted some really quality stuff this last week. I just wanted to provide another perspective. Relational database design & theory is the subject of countless tombs - it is impossible to cover all of it in a few forum posts. I hope we provided enough to give you some idea of the options you have and some impetus to look up some of these things yourself.

  11. #11
    Join Date
    Apr 2008
    Location
    All over, Now in Missouri
    Posts
    73
    I have always embraced all the comments on this forum and found countless ways to accomplish a task. The problem is with the non-tech people (bosses/coworkers) who blast off saying we need a product - without having any idea of what is involved in the background!!!
    We tend to look at Linear paths which can lead us to a path of resistance!

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is why it is crucial, as part of your job, to strongly emphasise that your role is to gather and understand business requirements. Flatter your clients - point out that they are experts at what they do, you know very little about it, and their role is to explain all the things (that they may take for granted) that makes up their position and responsibilities.

    It is not easy, it is not straight forward, but it is important. I have a few stock metaphors that I will use (for example, if you are building a house are you really going to tell the architect the proportion of sand to cement, or materials the lintels should be made from? No - you should be saying how many rooms you need, what size they should be, how they interconnect etc.). Don't be afraid to stop a requirements meeting dead if you feel you are losing control and make it very clear what the respective roles are of the participants. As a last resort, ask them that if they are determined to design the database themselves why exactly is your presence required?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •