Dev.Database Schema

Normalization

Naming conventions

• All in lowercase
• No hyphen, space
• Always use semantically meaningful names
• Naming tables/fields in singular
• Always define a auto_increment column "id", as primary key if applicable
• Do not user table name as prefix to field name
• Do not use preserved keywords, like "date"

Performance considerations

• Always specify NOT NULL if possible
• Use INT for date/time
• Use ENUM over varchar
• User BIT or tinyint(1) for bool data
• Specify varchar(n) as 8, 16, 32, 64, 255
• Specify UNSIGNED for integer types
• Index search fields
• Encode multiple data into text and store as key-value if they are not intended to use rationally
• Do not use triggers and stored routines, keep the logic in PHP

Attributes suggestion