Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2009
    Posts
    8

    Question Unanswered: Normalisation help required (was "Am I right!?")

    I posted before about my normalization problem, I have come back to it today and tried to noramlize it all myself. Im not sure if I have done it right and would like someone to look at it and maybe point out something I have done wrong.

    1NF:

    ID, Staff/Trainee, ProductType, Description, Tag no/Serial no, Computer Name, RoomLocation, Site, Owner, Seller, Date of Purchase, Date of Disposal, Warranty, Notes


    2NF:

    I.D, Type, Description, Tag no/Serial no, Computer Name, Seller, Date of Purchase, Date of Disposal, Warranty, Notes

    I.D, Staff/Trainee, RoomLocation, Site, Owner


    3NF:

    I.D, Type, Tag no/Serial no, Computer Name, Date of Purchase, Date of Disposal, Warranty, Notes

    Site, Staff/Trainee, Owner, RoomLocation

    I.D, Site

    Type, Seller, Description


    You probably know this but the fields in bold are the key fields and each new line is a new table.
    The only table I am really unsure about is the last one with type, and seller as the key fields. I am simply trying to reduce the size of the main table as it seems still rather big with these still in it. I know there are such things as foreign fields/keys and composite keys but Im not sure what they are.

    All help and criticism is welcome, thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have a hint for you in your normalization exercises --

    try to normalize ~without~ using the ubiquitous and nebulous "ID" concept

    in other words, for each entity, consider which data column(s) form a natural key

    and do use entity names to identify the entities (tables) in your model

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2009
    Posts
    8
    Sorry about not naming my tables but I actually do have an ID for everthing, each piece of equipment has a unique ID so I do have to put it in anyway. I think i Have something sussed now though.

    Instead of the last table (Type, Seller, Description) I have opted for the following table:

    Tag, Name, Description

    I have opted for this as only PCs will have names therefore in the main table there will not be a lot of blank space.

    I am probably doing this all wrong!!

  4. #4
    Join Date
    Apr 2009
    Posts
    8
    To put that a better way I have done this:

    1NF:

    tblInventory-
    ID, Staff/Trainee, ProductType, Description, Tag no/Serial no, Computer Name, RoomLocation, Site, Owner, Seller, Date of Purchase, Date of Disposal, Warranty, Notes


    2NF:

    tblInventory-
    I.D, Type, Description, Tag no/Serial no, Computer Name, Seller, Date of Purchase, Date of Disposal, Warranty, Notes

    tblLocation-
    I.D, Staff/Trainee, RoomLocation, Site, Owner


    3NF:

    tblItems-
    I.D, Type, Tag no/Serial no, Seller, Date of Purchase, Date of Disposal, Warranty, Notes

    tblLocation-
    Site, Staff/Trainee, Owner, RoomLocation

    tblSite-
    I.D, Site

    tblPCInfo-
    Tag no/Serial no, Description, Name

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by i8grand
    Sorry about not naming my tables but I actually do have an ID for everthing, each piece of equipment has a unique ID so I do have to put it in anyway.
    Rudy's point is actually to help you.

    There are some circumstances where you have to use some form of "made-up-identifier" (like for people - you can't use name or SSN or NI number etc as these are not unique).
    However, there is another time when made up numbers are used: they are used in place of "real-world-identifiers". Rudy's point is that IDs of this type actually make normalisation much more difficult. They have no place in data modelling.

    If you want to learn more, google "natural and surrogate keys". You could check these at the same time you check out "composite keys"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No need to be sorry for anything. It's a very old debate but one that's still very alive: natural versus surrogate keys.

    Personally I tend to flavour surrogate (or as I've sometimes heard "artificial" or "non-natural") keys. They are pros and cons on both sides of the fence.

    One thing I cannot agree with though, is the use of composite keys. I've made a lot of research on the net as well as in books on the subject but never could find a good convincing reason to use them. If one column alone is not able to provide a unique key, then what is sometimes called "non-natural" key becomes a natural one.

    Moreover composite keys are against the principle of atomicity: if you want to create a relation between a table with a composite key and another table, you're obliged to include two columns in the second table in order to create this relation.

    Have a nice day!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sinndho
    One thing I cannot agree with though, is the use of composite keys.
    have you ever implemented a many-to-many relationship?

    let's take the classic books-and-authors example

    each author can write multiple books, and a book can have multiple co-authors

    there are three tables -- books, authors, and book_authors, the third one being the relationship table that identifies which author(s) wrote each book

    there will be only two columns in the book_authors table, and each of them is a foreign key back to its respective table -- book_id and author_id

    thus, you can expect to see data like this --
    Code:
    book_authors
    book_id  author_id
     1004      23
     1005      27
     1005      29
     1008      23
    get it? books 1004 and 1008 have only one author (which happens to be the same guy), while book 1005 has two authors

    now here comes the coupe de grace: the primary key of this table is a composite key

    do you still disagree with using a composite key?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's far from being a coup de grâce, just a skirmish at best.

    First: the primary key of this table is a composite key. Why is that? Simply because you decided that the combination of those two columns will be the primary key of that table. Nothing refrains from creating a third column that would be the primary key.

    Second: To me it's not a good idea, mainly because the logic that can garantee the unicity of the key in this table relies on the application that uses it as well as on the construction of the table. If you can edit those columns, nothing can prevent you from trying to duplicate one of those composite keys. Of course you'll get an error, because you declared their association as being the primary key of the table. With an auto-gererated key (Autonumber, UID etc...) you would not be able to try to write anything in the column holding the primary key. Only the DB engine could do that. With your system the validity checking is made after you try to enter something into the columns, while with a system-generated key no checking is necessary at all.

    Have a nice day!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sinndho
    Nothing refrains from creating a third column that would be the primary key.
    yeah, you could do that, but it would be superfluous, redundant, and silly

    Quote Originally Posted by Sinndho
    ... the logic that can garantee the unicity of the key in this table relies on the application that uses it as well as on the construction of the table.
    wtf????

    you have pretty much convinced me that you don't really know what you're talking about

    Quote Originally Posted by Sinndho
    Have a nice day!
    you too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I dont like bragging but working as a dba in a multi-national company for more than 25 years proves me the contrary.

    Of course you're entitled to have your opinion.

    Have a nice day!

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I use ID's all the time, so I'm with you Sinndho.

    Yes, natural keys are better in a lot of ways. But when the code comes down to

    "WHERE ID = 4883"

    or

    "WHERE FieldA = ""This"" And FieldB = ""That"" And FieldC = ""TheOther"" And FieldD = ""AdNauseum"""

    It is then that both me and my customer are very thankful for superfluous, redundant and silly identifiers. Less stress and code for me, less cost for my customer.

    So what if it's not "Rudy Perfect"!!

    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As I wrote it's an old debate and I seriously doubt it's one that will find a definitive answer anytime soon.

    By the way, did you notice how quickly some people become passionate in such matters? Sometime you could believe you hear the rambling of some religious fanatic

    Have a nice day!

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There is an issue here though that is being missed. The question is about data modelling. Surrogate keys are the implementer's prerogative. Surrogate keys belong in databases (or not - depending on your particular view). They absolutely do not belong in a model.
    How the hell is a modeller (especially one working on their first model) to identify second normal form violations (let alone BCNF, 4th, 5th) if they use surrogate keys?

    Um...I've just read some of these posts. Sinndho - you do know that Surrogates are in addition to the natural key right? They purpose is exclusively for use in relationships (hence Rudy's point). That's why they are called surrogates - they "surrogate" for the natural key in the other tables in the database.
    Last edited by pootle flump; 04-10-09 at 06:22.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Sinndho
    One thing I cannot agree with though, is the use of composite keys. I've made a lot of research on the net as well as in books on the subject but never could find a good convincing reason to use them.
    I keep intending to write a big article on this. There are many applications I have written that took major advantage of composite keys (specifically clustered, composite keys) and that would have performed less optimally if I had used surrogate keys.

    Quote Originally Posted by Sinndho
    Moreover composite keys are against the principle of atomicity: if you want to create a relation between a table with a composite key and another table, you're obliged to include two columns in the second table in order to create this relation.
    I just choked. Actually, I think Rudy might be right, 25 years experience or not.
    BTW - you might want to look up the difference between "relation" and "relationship" - it is beyond fundamental.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    It is then that both me and my customer are very thankful for superfluous, redundant and silly identifiers. Less stress and code for me, less cost for my customer.

    So what if it's not "Rudy Perfect"!!
    You didn't understand Rudy's point. He was specifically talking about intersection (or association tables) with no attributes. Their only purpose is to enforce the relationship. In this case, even if I was the most ardent surrogate key fan, putting an identifier on this table would be "superfluous, redundant and silly".
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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