Storing id's or strings Mysql DB-CodePudding

I am having an issue that is bothering me for a year now, and I can't find a straight answer to it, so I am bringing it to the public in hope that it may get resolved and I can't be free for eternity.

So the actual question is - How should we store data in the database if we have predefined values in options?

Value as string or maybe?

<select name="cars" id="cars">
  <option value="volvo">Volvo</option>
  <option value="saab">Saab</option>
  <option value="mercedes">Mercedes</option>
  <option value="audi">Audi</option>
</select>

Values as intiger ?

<select name="cars" id="cars">
  <option value="1">Volvo</option>
  <option value="2">Saab</option>
  <option value="3">Mercedes</option>
  <option value="4">Audi</option>
</select>

My inner voice is saying that integer is the best option, any thoughts?

CodePudding user response：

that depemds on your structure.

If the cartype is a column in another table, you save the id only and have a second table where yousave cartypeid and cartype.

With this second table you can fill the Form, and only join the cartype table when you need the name.

in the case that cartype is UNIQUE as primary for example, you can send the name

CodePudding user response：

Your choices from the point of view of your database design.

Simply use the short text string, in your case the brand of automobile. Then when you want to populate your pick list, fetch it with
```
SELECT DISTINCT brand FROM cars ORDER BY brand;
```
Create a reference table, maybe call it brand:
```
brand_id   |  name
1          |  Volvo
2          |  Saab
3          |  Mercedes
4          |  Audi
5          |  Fiat
```
Then use that table to populate your pick list, and put brand_id in the cars table where you refer to the brand. It can be a foreign key to the 'brand' table.
Use an ENUM for the brand, defining its row in your table something like this:
```
brand ENUM('Volvo', 'Saab', 'Mercedes', 'Audi', 'Fiat')
```

Choice 2 is probably the best: it makes it trivial to add a new brand to the pick list: simply insert a row into the brand table. It "controls the vocabulary" of the brand: that is, it won't allow your cars table to contain misspellings or other junk. And, it's space-efficient and index-friendly for your cars table.

Choice 1 is OK too, but it lacks the advantages of Choice 2.

Choice 3 is not so good: For one thing it's fairly expensive to add a new brand to your system; you have alter your table. For another, retrieving the possible values of the brand to put in your pick list is harder than in the other two choices.