I'm optimizing table schema.
My case is like.
User can generate an article.
It has a lot of item. like name, age, sex, address, hobby…
name, sex is a required entry.
but age, address, hobby is not required entry.
Table has a lot of null currently.
Sometimes null is changed attribute domain value when user edit article.
name | age | sex | address | hobby
aa null male null soccer
bb 17 female null null
So I'm thinking 2 case.
1.
Table: article
Column: id, name, sex
Primary key: id
Table: article_sub
Column: article_id, type(0:age, 1:address, 2:hobby), value
Primary key: article_id, type
This case have to set varchar data type to value.
Because age is integer, address is string… so size is demerit.
2.
Table: article
Column: id, name, sex
Primary key: id
Table: article_sub_integer
Column: article_id, value
Primary key: article_id
Table: article_sub_string
Column: article_id, type(0:address, 1:hobby), value
Primary key: article_id, type
This case I have to refer 3 table.
Which way is the better?
If you have other idea Please tell me.
Thank you.
Best Answer
If you are short of storage and database is big enough you can reduce the size ot table but the price is significantly higher CPU and RAM consumption. Just split the table
into the two tables:
Each time you need the full info do a
JOIN..ON
LEFT JOIN
fill description column byNULL
if no according row in theP_DESCR
table for certainPERSON
Both approaches - with
NULL
s andJOIN
s are valid but you can choose what is more suitable for your needs.