CODEWITHSUNDEEP
mysqlsqldatabase-normalization
Merc
Merc

Reputation: 17057

Should I normalise these fields?

I am torn. I am dealing with data that is very difficult to deal with; a "job" has at the moment over 100 columns.

I put all of the columns into the job because every time I get a job's info, I will 99.99% of the time need all of the data. So, splitting it would probably get me better grades if I were a student, but it would simply resolve into joints every time I load the data.

One example I find it hard to decide is cargoes. A ship can have one (80% of the time), 2 (99% of the time) or 3 (1% of the time) cargoes. Never 4. Storing cargoes in a 1:n relationship with the job is very easy, but it also means that:

However, now I have these columns in my DB:

cargoId1, cargoDescription1, contractTonnage1,
contractTonnageTolerance1, commentsOnTonnageTolerance1, 
tonnageToBeLoaded1, tonnageLoaded1

cargoId2, cargoDescription2, contractTonnage2, 
contractTonnageTolerance2, commentsOnTonnageTolerance2, 
tonnageToBeLoaded2, tonnageLoaded2

cargoId3, cargoDescription3, contractTonnage3, 
contractTonnageTolerance3, commentsOnTonnageTolerance3, 
tonnageToBeLoaded3, tonnageLoaded3

What would you do? Ideas?

Upvotes: 0

Views: 61

Answers (1)

George Menoutis
George Menoutis

Reputation: 7240

I'll have to warn you that you will probably get downvotes, close votes and/or delete votes for a "primarily opinion-based" question. I think your question IS primarily opinion-based, as it is essentially synonymous with "pros and cons of normalization". (ps: I hate the fact that this should get you downvotes though).

One thing you could do if you would like to have the best of both worlds is to make the table normalized, and create a view that will return the de-normalized form with PIVOT. This way, the integrity of your data gets better from normalization, and WRITING a query will be easier. Joins that will (slightly with a good index) affect performance will be done, but imo that's a small price for integrity.

Upvotes: 1

Related Questions