CODEWITHSUNDEEP
mysqldatabasedatabase-design
Ayush
Ayush

Reputation: 42450

Set up Table to store variable number of fields per record?

How should I set up my database / table, if I do not know the number of fields I would populate per record?

For example, if I have a web form that allows a user to enter all the cars he owns, and I don't want to limit him to a certain number, how would I store this in the database end?

The above problem extends to similar situations such as storing a user's order (variable number of items per order) etc.

Upvotes: 6

Views: 3909

Answers (2)

Xint0
Xint0

Reputation: 5389

In Relational Database Management Systems (RDBMS) instead you create child records in a dependent table that relate child entities (cars) with parent entities (users). There is a concept known as database normalization, and the objective is that each table contains data for a single type of entity.

So you have a user table with the user information:

user_id | user_name | email             | ...
  1234  | User1     | [email protected] | ...
  2356  | User2     | [email protected] | ...

Then another table for storing the information of each car of a user:

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  | ...

So instead of storing the info of the cars in the user table, you have a table for storing car (user_car) information related to each user by way of the user_id column. This is an example of a one-to-many relationship, in which one user can have many related cars.

Upvotes: 9

Randy
Randy

Reputation: 16677

this is an entire topic: database normalization.

the short answer is you make more than one table.

in your example you would have person table, a car table, and a third that linked person to the car

Upvotes: 3

Related Questions