MySQL - Represent an array

Question

I want to create a database structure that can store data about families (primary key, name and its members).

I came up with the idea of doing it using these two tables:

TABLE "family":
- id (INT)
- name (VARCHAR)
- members (?)

TABLE "members"
- id (INT)
- name (VARCHAR)

I would like to reference members of the family by the id in the members table. But since MySQL doesn't have arrays, how am I supposed to store multiple members in one column? Or are there better DB structures for this case?

Kenta1561

You can simply have a foreign key in the members table that points to the family table. — Barmar
– Barmar, Commented May 6, 2017 at 13:55

Gurwinder Singh · Accepted Answer · 2017-05-06 13:55:29Z

1

Don't try to store multiple value in a single cell as arrays. It'll be nightmare for you later when you have to search rows based on one of the value in those arrays or join table with that column and other operations of the sort.

You can create a separate mapping table for that to keeps the things normalized.

You can create a separate table family_members with, say, three columns:

id (auto increment), 
family_id (FK to family table), 
member_id (FK to members table)

Or have an extra column in the members table as FK to family table if there is one to many mapping.

The mapping table helps if there can be many to many mappings.

answered May 6, 2017 at 13:55

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Minhaj Patel · Accepted Answer · 2017-05-06 14:00:36Z

1

Create Foreign key to Member_Id in tblFamily table

TABLE "tblFamily":
- Fam_Id (INT)
- Family_Name (VARCHAR)
- Member_Id (INT)

Create Primary key to Member_Id in tblMember

TABLE "tblMembers"
- Member_Id (INT)
- Member_Name (VARCHAR)

answered May 6, 2017 at 14:00

Minhaj Patel

5771 gold badge7 silver badges21 bronze badges

Comments

Nishesh Pratap Singh · Accepted Answer · 2017-05-06 14:09:16Z

0

You can try below structure :

Table "Family"
- FAMILY_ID INT (PK)
- FAMILY_NAME VARCHAR

Now as your family can have multiple members, so instead of storing member_id in family table, it will be good to store FAMILY_ID in MEMBERS table.

Table "Member"
- MEMBER_ID INT (PK)
- MEMBER_NAME VARCHAR
- FAMILY_ID INT (FK) REFERENCES FAMILY.FAMILY_ID

This way you will be able to store multiple members for a family and using join on these tables will be able to print all information about the family in single query.

answered May 6, 2017 at 14:09

Nishesh Pratap Singh

2,1912 gold badges15 silver badges21 bronze badges

Collectives™ on Stack Overflow

MySQL - Represent an array

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related