mysql table structure proposal?

Question

is this table any good for mysql? I wanted to make it flexible in the future for this type of data storage. With this table structure, you can't use a PRIMARY KEY but an index ...

Should I change the format of the table to have headers - Primary Key, Width, Length, Space, Coupling ...

ID_NUM  Param   Value
1   Width   5e-081
1   Length  12
1   Space   5e-084
1   Coupling    1.511
1   Metal Layer     M3-0
2   Width   5e-082
2   Length  1.38e-061
2   Space   5e-081
2   Coupling    1.5
2   Metal Layer     M310

Answer 1

No, this is a bad design for a relational database. This is an example of the Entity-Attribute-Value design. It's flexible, but it breaks most rules of what it means to be a relational database.

Before you descend into the EAV design as a solution for a flexible database, read this story: Bad CaRMa.

More specifically, some of the problems with EAV include:

• You don't know what attributes exist for any given ID_NUM without querying for them.
• You can't make any attribute mandatory, the equivalent of NOT NULL.
• You can't use database constraints.
• You can't use SQL data types; the value column must be a long VARCHAR.
• Particularly in MySQL, each VARCHAR is stored on its own data page, so this is very wasteful.

Queries are also incredibly complex when you use the EAV design. Magento, an open-source ecommerce platform, uses EAV extensively, and many users say it's very slow and hard to query if you need custom reports.

To be relational, you should store each different attribute in its own column, with its own name and an appropriate datatype.

I have written more about EAV in my presentation Practical Object-Oriented Models in SQL and in my blog post EAV FAIL, and in my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

Answer 2

What you suggest is called EAV model (Entity–Attribute–Value)

It has several drawbacks like severe difficulties in enforcing referential integrity constraints. In addition, the queries you'll have to come up with, will be a bit more complicated than with a normalized table as your second suggestion (table with columns: Primary Key, Width, Length, Space, Coupling, etc).

So, for a simple project, do not use EAV model.

If your plans are for a more complex project and you want maximum flexibility, do not use EAV either. You should look into 6NF (6th Normal Form) which is even harder to implement and certainly not an easy task in MySQL. But if you succeed, you'll have both goods: flexibility and normalization to the highest level (some people call "EAV" as "6NF done wrongly").