SQL Normalization issue

  • I'm having issues normalizing http://i.imgur.com/pLH07VJ.png and it's really bothering me. I can see that the table is in first normal form but from there I'm stuck. My usual method is to find partial dependencies by checking for consistencies in each row in reference to a primary key. The problem with this table is both:

    1. The first column only contains unique data and so it seems like it could be used as a primary key for the whole table. I've only ever normalized tables with a composite primary key but it seems pointless to do that here as the first column works fine as a primary key on its own.

    2. There's no consistencies in the rows. The screens all have different movies which have different prices/dates etc. Only the customerID/customername are consistent.

    I don't know if it's a proper technique but I thought about normalizing by referring to the deletion anomaly and organizing the tables so that if the session is deleted, the information about the branch is preserved. Using this I can get to something that resembles 3rd normal form but I don't think it's correct.

  • IMO deriving key candidates and normalizing tables by only looking at the data is problematic. Especially when you only have a few data rows to work with.

    The customerID/customername thing you mentioned is pretty clear.
    Regarding the rest: looks OK, but to be sure one would have to know what the various fields mean.

    You have to know what the entities are and what relationships exist between them. If you know that, defining the tables is pretty straight forward. Starting with the data, restructuring the tables by following the normalization rules, and then finally deriving what entities exist in the system (and what relationships exist between them), IMO, is working in reverse.

Log in to reply