1

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.

  • 4
    Which way is the better? The best way is do nothing I think. Why you are so afraid of nulls? – Akina Jul 4 '18 at 8:06
  • 1
    @Akina I think Null doesn't satisfy normalization. – jonggu Jul 4 '18 at 8:54
  • 1
    @EzLo Thank you for your advise. "optional field can be null" – jonggu Jul 4 '18 at 8:56
  • 2
    Null doesn't satisfy normalization Why??? Normalization says nothing about specific values (both existing and absent). – Akina Jul 4 '18 at 8:59
  • 2
    Optimizing for what? You say "null is bad for normalization" and then you propose two solutions which are horrible for normalization. Especially the one where you separate the columns based on type. It's also unclear how an article can have an age, sex, and a hobby. That sounds more like a user/person type of table. You should probably work out the schema so it's logically correct and properly normalized. Don't know much about MySQL internals (vs. Postgres), but this "optimize for nulls" thing seems unnecessary or at the least premature. – Kayaman Jul 4 '18 at 9:44
0

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.

  • Null flag needs 1 byte per each nullable field per record. Additional P_ID field takes more (I think). Where is a size profit? And don't forget about additional volume for an unique index by P_ID field... – Akina Jul 4 '18 at 9:03
  • @Akina This is true because default row format for InnoDB is compact by default. – Kondybas Jul 4 '18 at 9:11
  • Additionally the storage block-granularity causes the table size may both decrease and not decrease after nullability removing. – Akina Jul 4 '18 at 9:16
  • @Akina I prefer CREATE TABLE..SELECT instead of ALTER TABLE in such cases. – Kondybas Jul 4 '18 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.