TransWikia.com

Article table has a lot of null. What is the best way to prevent null in MySQL

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.

One 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

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 NULLs and JOINs are valid but you can choose what is more suitable for your needs.

Answered by Kondybas on January 4, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP