Machine Database - for mysql

Question

I have a dilemma regarding my database design for my car system (my father has a dealer and I want to create a new system for him where the user can plan test drives, apptointmen, etc.)

I am not sure between these two models:

table car id int, model_id int, make_id int, price float, year year, millage int, etc... 

or

 table car id int, model enum, make enum, price float, year year, millage intetc... 

client table with (id int, name varchar, phone int, street varchar, city, etc.

salemen table with (id int, varchar name, etc.)

schedule table (id int, appt_Date datetime, car_id, customer_id, salemen_id)

now my question is this:

Should I use enum fields or do I have a table for each enum field? I do not want to redesign the system when it is used by everyone, and start all over again.

thanks

Answer 1

I would stay away from ENUM for one main reason. If you want to add new elements, you must add ALTER to the table. What can be painful if you have a lot of data. Also, depending on the sorting and character set you use, be careful with case sensitivity.

Read this article for more information: 8 reasons MySQL Enum Data Type Is Evil .

Now using referenced data / table using a foreign key is preferable because you can have a data integrity rule. You can create a simple tool to manage this data (make or model) without changing or changing any database schema.

Answer 2

My preference would be the first option. use int as a foreign key for the corresponding other tables. thus, you add rows to these other tables, and the "enum" values ​​are automatically present.

you can publish the proposed tables at some point to get some help with normalization in general :)

Answer 3

I have never used enum fields.

Most likely, you will build an application on top of this using a tool such as PHP. You can get values ​​from the database using the method described at http://barrenfrozenwasteland.com/?q=node/7

This is a bit more inconvenient than getting possible values ​​from a separate table.

You will have a little more flexibility with a separate table, since you do not need to give privileges to web users to update the table definition. The enumeration field also has a limit of 10,000 different values.

Answer 4

Do not use ENUM .

Why? 8 reasons.