CODEWITHSUNDEEP
sql-serverdatabase-designobject
daveomcd
daveomcd

Reputation: 6555

How do I properly created a 1 to many relationship in my database?

If I have the following tables (with PKs in brackets)...

Store { [StoreCode], PhoneNumber, Address, ...}

StoreHours { [id], DayofWeek, Open, Close, ClosedDate }

Now my thinking is every store wont have the same hours & they wont have the same hours everyday, and instead of making multiple columns in the Store table such as, "OpenTimeA, OpenTimeB, OpenTimeC, CloseTimeA, CloseTimeB, ...", then I could create a relationship table. However, I'm not used to doing them and want to go ahead and get it right the first time. Here's what I was thinking of adding to the above tables...

Has_a { [StoreCode, id ] }

My questions are if I do it this way...

  1. Is there a better way to name the table than Has_a or can I name it that and use it for multiple different types of relationships for the store. For instance, add another table Managers and add a column to the Has_a table for manager ids.

    like...

StoreInformation { [StoreCode], TimeId, ManagerId, ...}

Upvotes: 0

Views: 55

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

For the first question, one possible solution is (standard 1-to-many relationship):

Store 
   StoreCode 
   PhoneNumber 
   Address
   ...
   PRIMARY KEY (StoreCode)

StoreHours 
  StoreCode 
  DayOfWeek 
  OpenTime 
  CloseTime
  PRIMARY KEY (StoreCode, DayOfWeek)
  FOREIGN KEY (StoreCode)
    REFERENCES Store(StoreCode)

If you notice that you have many Stores that open and close at the same times, all days of the week, you can add a Schedule table, so multiple Stores can share the same Schedule.

Store 
   StoreCode 
   ScheduleId
   PhoneNumber 
   Address
   ...
   PRIMARY KEY (StoreCode)
   FOREIGN KEY (ScheduleId)
     REFERENCES Schedule(ScheduleId) 

Schedule
  ScheduleId
  ScheduleTitle
  PRIMARY KEY (ScheduleId)

ScheduleHours 
  ScheduleId 
  DayOfWeek 
  OpenTime 
  CloseTime
  PRIMARY KEY (ScheduleId, DayOfWeek)
  FOREIGN KEY (ScheduleId)
    REFERENCES Schedule(ScheduleId)

Upvotes: 2

ckramer
ckramer

Reputation: 9443

The biggest problem with creating a generic Has_a table for tracking these sorts of relationships is the fact that you will not be able to use a foreign key relationship between your tables, since you can only relate a single source table to a single destination table. In addition to that, unless your using GUIDs for your keys, there is no way to know which table a particular key belongs to, so you are likely to get bogus results.

Also, for a one to many relationship this sort of intermediate table is not needed. You can add a StoreCode column to your StoreHours table with the appropriate foreign keys. The only case where you need the intermediate tables are many-to-many relationships, which is probably not needed in this case (unless you want to have multiple stores share the same StoreHours record).

Upvotes: 2

Related Questions