0

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.

Luiggi Mendoza
  • 85,076
  • 16
  • 154
  • 332
mr.nothing
  • 5,141
  • 10
  • 53
  • 77
  • 2
    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](http://stackoverflow.com/tags/sql/info) – Luiggi Mendoza Apr 21 '13 at 14:44
  • @LuiggiMendoza, thanks, I'll take a look on these tutorials. – mr.nothing Apr 21 '13 at 15:13
  • 1
    Since you said you are new to database design, I've heard good things about the book, Database Design for Mere Mortals. – Dan Bracuk Apr 21 '13 at 15:18
  • @DanBracuk, Thank you! That one goes to my "to read" list. Love Stackoverflow :) – mr.nothing Apr 21 '13 at 15:29

2 Answers2

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].

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
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
Kapil Khandelwal
  • 15,958
  • 2
  • 45
  • 52