Mysql – How to normalize a database which uses tables as references to other tables

MySQLnormalization

I'm trying to create the database for my application, but I cannot manage to normalize my data on a MySQL database.

  • I have a Types of map entity

  • The Types must have one or more Models associated to it, in a particular order

  • The Models have a Grid, is_prediction flag and an Origin associated to it

  • There can be more than one Model using the same Grid, Origin and is_prediction condition, differing only on the name of the model

  • Not all Origins provide all the Models

  • The Types can only have Models associated to it that have the same condition of [Grid, Origin, is_prediction]

I tried to create a table types_hierarchy, using grid_id, origin_id and is_prediction as foreign key, but it seems wrong, according to the answer on my other question here.

How can I create a normalized database for my needs?

This is what I tried to do:

diagram

Best Answer

As per your other post, if you require such delete requirements, it is surely a poor design.

Secondly, there is no "so called" The Best Design.

Third, do not get struck with heavy normalization though essential. Design in such a way that you arrive at a maximum normalization and put it to use. While Beta testing...you shall refine to an extent.

And finally, even if we review / amend your design details, that might not be a best match for your exact requirement. instead, with your given details, if you would have added any particular issue which you foresee, we shall advise on the same.

Related Question