CODEWITHSUNDEEP
sqldatabasenormalization
Hayden null
Hayden null

Reputation: 1

Relational database normalization

I'm having issues normalizing this table 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. 

Session | Branch | Screen | Movie | Session   | Session | Customer | Customer
ID      | ID     | ID     | ID    | Date      | Price   | ID       | Name
SS01    | B1     | S1     | M1    | 3-May-16  |   12.50 | C1       | Chris Hemsworth
SS02    | B2     | S1     | M2    | 4-Jun-16  |   19.45 | C2       | Chris Evans
SS03    | B1     | S2     | M2    | 3-May-16  |   12.67 | C2       | Chris Evans
SS04    | B4     | S2     | M4    | 13-may-16 |   14.56 | C3       |Tom Hiddleston
SS05    | B3     | S2     | M5    | 23-may-16 |   14.56 | C2       | Chris Evans
SS06    | B3     | S1     | M5    | 3-Jun-16  |   16.32 | C1       | Chris Hemsworth
SS07    | B4     | S2     | M3    | 14-May-16 |   21.78 | C2       | Chris Evans
SS08    | B1     | S2     | M2    | 6-Jun-16  |   16.82 | C2       | Chris Evans 
SS09    | B2     | S3     | M4    | 13-May-16 |   17.90 | C1       | Chris Hemsworth 
SS10    | B4     | S1     | M3    | 6-Jun-16  |   16.37 | C3       | Tom Hiddleston

Upvotes: 0

Views: 88

Answers (1)

Sturgus
Sturgus

Reputation: 686

It looks to me like this table is in pretty good shape (assuming it's supposed to be a table describing the 'Session'). The only redundant column I see is "customer name", but everything else appears to be necessary. 'Session Price' may or may not be a calculated column (based on the other columns).

Without knowing the business logic, I can't confirm whether or not the 'branch' (or other things) needs to be in there or not.

Edit: Hypothetically, you could remove the 'session id' column as the pk and add a compound key instead (e.g.: branch + screen + movie + session date + customer id), but that won't necessarily make it better. Instead, a uniqueness constraint on those columns may be preferred.

Upvotes: 1

Related Questions