CODEWITHSUNDEEP
javasql
mr.nothing
mr.nothing

Reputation: 5399

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

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.

Upvotes: 0

Views: 2183

Answers (2)

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

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

Upvotes: 3

JB Nizet
JB Nizet

Reputation: 691755

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

Upvotes: 4

Related Questions