CODEWITHSUNDEEP
mysqldatabasenormalization
spectralbat
spectralbat

Reputation: 407

Is this unnecessary normalization?

I have a few MySQL tables in a snail management game: users, races, jars, snails. The snail table had so much information dumped into it (from genetic string information to functional attributes like speed and endurance to visual attributes like shellColorR, B, and G, mating attributes like arousal delay, heritage attributes like parent snail IDs etc) that I would like to split it up into multiple tables in some sort of logical way.

I was considering splitting up all of this information into different categories and have a table for the following (with snail ID being primary key for each):

However, while that makes sense in my head to organize these into separate thematic tables - in practice it doesn't seem like it would really provide that much benefit. I'm not avoiding any duplicate data - each table would have a unique record for each snail.

What is the best way to structure this? Should I just have all of the above in one big snail table or is there a better way of organizing this data?

Upvotes: 1

Views: 93

Answers (4)

user207421
user207421

Reputation: 310884

Unless a snail can have more than one row in each of the last five tables, it isn't 'normalization' at all: it's just unnecessary table splitting.

Upvotes: 2

Max
Max

Reputation: 2552

For what is my experience in DB design, you can keep this data in the same table. Splitting into different tables won't give you any advantage. You'll likely end up in just more space allocation: I suppose you have an index build on your PK, so you'll get six indexes instead of just one.

Upvotes: 1

Lee S
Lee S

Reputation: 343

Loving the idea - there's just something that always makes me smile about snails! But I digress ....

There isn't a right or wrong answer to this, really. If there are always 1-1 relationships, then one big table isn't a problem. Would it make your code and/or database more readable to split the tables up? The usual reasons for splitting don't seem to apply here.

Upvotes: 1

changtung
changtung

Reputation: 1624

don't divide into tables if normalizaition rules don't force You to that. Create normalized database and print a diagram to it or create a note. Then you may create something like model and getAttribute method, which has attribute name argument.

Upvotes: 1

Related Questions