Database Administrators Asked by jonggu on January 4, 2021
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.
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
PERSON
+----+------+------+-------------+
| ID | name | attr | description |
+----+------+------+-------------+
into the two tables:
PERSON
+----+------+------+
| ID | name | attr |
+----+------+------+
P_DESCR
+------+-------------+
| P_ID | description |
+------+-------------+
Each time you need the full info do a JOIN..ON
SELECT *
FROM person AS w
LEFT JOIN p_descr AS z ON z.p_id = w.id
WHERE w.id = 1234
;
LEFT JOIN
fill description column by NULL
if no according row in the P_DESCR
table for certain PERSON
Both approaches - with NULL
s and JOIN
s are valid but you can choose what is more suitable for your needs.
Answered by Kondybas on January 4, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP