Set up a table to store a variable number of fields per record?

Question

How do I set up my database / table if I don’t know the number of fields that I would populate for writing?

For example, if I have a web form that allows the user to enter all the cars that belong to him and I don’t want to limit it to a certain number, how can I save it at the end of the database?

The above problem applies to similar situations, such as saving a custom order (variable number of items per order), etc.

Answer 1

In relational database management systems (RDBMSs), instead you create child records in a dependent table that link child objects (cars) to parent objects (users). There is a concept known as database normalization, and the goal is that each table contains data for one type of entity.

So, you have a user table with user information:

 user_id | user_name | email | ... 1234 | User1 | [e-mail] | ... 2356 | User2 | [e-mail] | ... 

Then another table for storing information of each user’s car:

 user_car_id | user_id | car_label | make | model | ... 1 | 1234 | MyCar | Ford | 2011 | ... 2 | 2356 | A Car | Chevrolet | 2010 | ... 3 | 1234 | MyOtherCar| BMW | 2000 | ... 

Therefore, instead of storing information about machines in the user table, you have a table for storing information about the machine ( user_car ) related to each user using the user_id column. This is an example of a one-to-many relationship in which one user can have many connected cars.

Answer 2

this is the whole topic: database normalization.

short answer: you create several tables.

in your example, you will have a table for people, a car table and the third one that connected the person to the car