CODEWITHSUNDEEP
mysqldatabase-design
Mas Bagol
Mas Bagol

Reputation: 4627

How do you store undefined number of things in MySQL?

Say, we have data of person that have some possessions. The person can have nothing or have anything. What is the proper way to store data like this?

As far as I know, MySQL doesn't provide a way to store array as a data type. If it does, maybe it will be something like this:

CREATE TABLE person (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    possessions ARRAY NOT NULL
);

Upvotes: 0

Views: 264

Answers (1)

Stephen S.
Stephen S.

Reputation: 1656

I believe what you would be looking for is three tables (or two if each possession can only be owned by one person).

  1. Table Person has PersonId (INT PK Identifier) and PersonName (along with any other person attributes)
  2. Table Possession has PossessionId (INT PK Identifier) and PossessionName (along with any other possession attributes)
  3. Table PersonPossession has PersonId and PossessionId representing that the person has that possession.

This is a fairly simple design for an m:n relationship (i.e. a person has some number (possibly 0) of possessions, and possessions have some number of "owners")

If each possession can only be owned by a single person you can go down to two tables by eliminating table PersonPossession and just having PersonId being a column of the Possessions table

Upvotes: 3

Related Questions