how to design a table to access the different places

advertisements

The tables are for users to access different places

The design as below:

user table:

<user>
userid
username
place          (the row define access rights)

place table:

<place>
placeid
placename
floor

My thoughts:

three places and placeid are 001,002,003 one user and userid is 001 to aceess these three places

<user>
userid     username       place
001        john           001,002,003

<place>
placeid    placename   floor
001        A           1
002        B           2
003        C           3
004        D           4

My question is,

in "user" table, the attribute "place" contains many placeids, and separate by a comma, this design is fine or bad ? It needs to separate the place values from "user" table ?


Using a comma delimited list to do a many to many relationship is bad design. You should use an intermediate table instead:

<user>
userid
username

<place>
placeid
placename
floor

<accessrights>
userid
placeid

Instead of putting "1,2,3" in user.place for userid 001, then, you put three rows in accessrights, all with userid 001 and one with each placeid.