when should separate field to another table(normalize) in database design?

Question

when should separate field for

1. filtering search?

EDIT: 2. in general when should I separate them (at least in example below brand, country, type, operation system are candidate and capable of being in separate table)

for example I have product table and I want to have filter on :

• screen size
• brand
• color
• country build
• type (tablet, smartphone, simple,)
• operation system
• camera pixel
• price
• and so on..

for what fields I should have separate table with many to one relation?

Answer 1

If you want to completely normalize everything and avoid redundant data, and simply use foreign key references in your main table, then create separate tables for all repetitive product properties. Which would be pretty much everything except "price" in the sample list you mention.

Otherwise, just for purposes of search filtering, you don't necessarily need to have any of them in separate tables. Just stack up WHERE conditions that you match against a single table. What you're on about is less of a search issue and more of a general database and back-end design matter.

Also be aware that if you need to match many search conditions at once, then your queries may end up becoming expensive with multiple joins, if you've separated your data into many tables. You can only create multiple-column indexes inside a single table, and you'll probably want to have joint indexes for the most common types of product property combo queries.

Answer 2

For a large "product table", the textbook use of normalization (single place for changing a common string) does not apply. Instead, you need to balance space (redundant storage) and speed (search cost) and convenience (KV schema causes ugly code).

Do not 'normalize' continuous values such as price, screen size, pixels, float, date, or other simple numeric values. This will be especially bad if you need to search by, say, a WHERE screen_size BETWEEN 20 AND 30 but you had normalized it.

Do not normalize "small" things. Example: a 2-letter country code (CHAR(2) CHARACTER SET ascii). You may need a table of more info about countries, but use the 2-letter code as its PRIMARY KEY.

For small, static, lists, consider ENUM. Eg, gender ENUM('unknown', 'female', 'male', 'other').

For money, use DECIMAL. For whole numbers, use TINYINT, SMALLINT, etc, depending on the maximum value. Also use UNSIGNED whenever appropriate.

Don't have too many columns. Instead, add a kitchen sink, that is, a JSON column with all the rare junk key-value columns. Price, brand, color are rather common; have columns for them. But screen size, pixels, and OS probably belong in the kitchen sink. No, you can't use MySQL to search the kitchen sink -- Use MySQL to filter on the visible things (price, etc), then use your app (in PHP or whatever) do decode the JSON and finish the filtering.

Or use MariaDB's Dynamic Columns.

More discussion in my blog on EAV.