Search code examples
mysqlsqldatabasecreate-table

Creating a Table in SQL, where each tuple can have mutiple values

I m try to make a Table using Create Table in SQL, where a person can work at multiple places, and a place can have multiple person working on it, this is what i m trying, i m sure its not correct

      create table ( person char(15), place char(15), salary int)

now since a person can work in multiple places, i m confused should the tuple place has multiple values, if yes. how do i do it Thanks in advance

Solution

  • You should create three separate tables:

    "persons"
int ID (primary key, auto-increment)
varchar username
varchar email ... (all other info needed)

"places"
int ID (primary key, auto-increment)
varchar name
etc.

    And the third table gives you the relationship between the two:

    "person_places" (or place_persons, depends on what you like)
int ID (primary key, auto-increment)
int place_id (linked to the ID of the "places" entry)
int person_id (linked to the ID of the "persons" entry)

    This way, every time a person starts working in a new place, you just add an entry to the "person_places". Same thing when they leave a place, or a place goes out of business or whatever, you just need to touch the "person_places" table.

    Also, this way, one person can work in several places, just like one place can have several people working in it.