SQL/Database: store array in the table's column

Question

Is there a canonical way of how to store array/list of something in column of sql table? I'm trying to do the following: I have "Building" and "Room" objects and Building object have Set as it's field. How such a relation (Building has a lot of rooms) can be described in terms of SQL?

I'm kind of newbie in database design, so, please, don't be angry if it's too trivial.

p.s. Also It would be great if somebody can explain how can relation "a lot of bus drivers use a lot of buses" and vice versa.

Usually, you don't store an array in a database. You establish a one to many relationship between two tables. Take into account that this concept (and many others) are heavily covered in a database tutorial book, there are some resources on StackOverflow sql wiki — Luiggi Mendoza
– Luiggi Mendoza, Commented Apr 21, 2013 at 14:44
@LuiggiMendoza, thanks, I'll take a look on these tutorials. — mr.nothing
– mr.nothing, Commented Apr 21, 2013 at 15:13
Since you said you are new to database design, I've heard good things about the book, Database Design for Mere Mortals. — Dan Bracuk
– Dan Bracuk, Commented Apr 21, 2013 at 15:18
@DanBracuk, Thank you! That one goes to my "to read" list. Love Stackoverflow :) — mr.nothing
– mr.nothing, Commented Apr 21, 2013 at 15:29

JB Nizet · Accepted Answer · 2013-04-21 14:53:58Z

4

For your first association, (One Building has Many Rooms), you would typically model this with two tables (building and room), where the child table (room) would have a foreign key to the parent table (building)

building
id (PK), name, location

room
id(PK), building_id (FK referencing building.id), room_number

For the second association, it's a many to many association, and you would need a join table between bus and driver:

bus
id (PK), plate_number, color

driver
id (PK), first_name, last_name

driver_drives_bus
driver_id (FK referencing driver.id), bus_id (FK referencing bus.id)

The primary key of this join table would be the couple [driver_id, bus_id].

answered Apr 21, 2013 at 14:53

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

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

Comments

Kapil Khandelwal · Accepted Answer · 2013-04-21 14:59:21Z

3

I think you should consider reading this article.

Refer This: SQL for Beginners: Part 3 – Database Relationships

Database relationships

One to One Relationships
One to Many
Many to One Relationships
Many to Many Relationships
Self Referencing Relationships

answered Apr 21, 2013 at 14:59

Kapil Khandelwal

16.2k3 gold badges49 silver badges55 bronze badges

Collectives™ on Stack Overflow

SQL/Database: store array in the table's column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related