Curious How To Store This Data


(Bharter) #1

I have a model of a client.

One attribute they have is membership to an club.

Any one client can be a member of 1,2…10 clubs.

I could do create a table for client_club (client_id, club_id)

But I am curious if it is possible to store this as an array in one column of the client table.

Client

(id int

,name varchar(50)

,club array( club_id)

);

Any suggestions?

Thanks!


(Alirz23) #2

you can use a text field and save it as comma separated values, or alternatively you can save it as a json object.


(Sharmakiran71) #3

From normalization point of view,

store different row for different club for different client.

client_id club_id

1------------>1

1------------>2

1------------>3

2------------>2

2------------>3

and so on…


(Jsfousseni) #4

"A picture is worth a thousand words"


(Bharter) #5

Thanks Alirz23,

I was thinking comma seperated values but json might be a better option…I am new to the json manipulation but was just curious if anyone was doing anything similiar.

I will play with it…

Thanks again!


(Bharter) #6

Thanks Fouss

Maybe better would be Client —< Client_Club >— Club

As I only want one Client record.

Just was curious if the club data could be embedded into the Client.

I will have to experiment a bit with JSON.


(Kburton) #7

I’d definitely use a separate joining table and a many-many relationship. That way you can join the tables together directly in queries and make use of the database engine’s query optimisations.

You can also navigate from client to club and from club to client easily. Using json, you’d have trouble navigating both ways.


(Bharter) #8

Thanks Keith ! That is what I would do in the Oracle world where I normally live…Just learning this Yii stuff, but it is pretty cool!