1

Hi I would like to ask when should I use Pivot table? My colleague are discussing why should we use pivot table instead of directly adding the relationship to another table below is a sample of a direct approach and I'm not sure if this is better than using a pivot table.

This is a one-many relationship. A branch can have many store

Table1: Branch
Field:[
   id
   name
   address
]

Table2: Store
Field: [
   id
   branch_id **[foreign_key related to branch table]**
   name
   fee
   location
   type   
]

So I was thinking is it better to use the above structure or use the below approach which makes use of a pivot table

Table1: Branch
Field:[
   id
   name
   address
]

Table2: Store
Field: [
   id
   name
   fee
   location
   type   
]

Table3: Branch Store
Field: [
   id
   branch_id **[foreign key related to branch table]**
   store_id **[foreign key related to store table]**
]

Our main concern is the query speed for this as the second approach will need to make use of another(third) table just to retrieve or relate the two tables. But the first approach will only make use of two table

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
MadzQuestioning
  • 3,341
  • 8
  • 45
  • 76
  • It somewhat depends -- is this a `0-1` relationship or a `1-many` relationship? In other words, can a store have multiple branches? – sgeddes Aug 04 '16 at 03:34
  • The second example is not a `pivot table` but a `joining table`, and they are widely used to represent M:N relations. A `pivot table` is an excel/spreadsheet concept. Not to be confused with just `pivot` which is a SQL command, but not a table type. – alzee Aug 04 '16 at 03:34
  • @user3137702 yeah I just can't find the right term to use. Will edit this – MadzQuestioning Aug 04 '16 at 03:39
  • @sgeddes A branch can have many store. So its a one-many relationship – MadzQuestioning Aug 04 '16 at 03:40
  • I've only ever heard them called `joining tables` or `connection tables`. **Pivot** has a special meaning in SQL so really shouldn't be used here. You also won't need the id column in the joining table, it has a natural PK formed by `(branch_id, store_id)` – alzee Aug 04 '16 at 03:44
  • Shameless plus for [Junction Tables](http://stackoverflow.com/a/32620163). They use indexes. They are fast. – Drew Aug 04 '16 at 03:46
  • Your original design is good, no need a third table. A pivot table is used when there is a many-many relationship – SIDU Aug 04 '16 at 03:53
  • Your main concern is query speed,so go for first approach where you can get data in single query by performing join on both the table.however you can perform join on 3 table as well but I think first approach is better – Saurabh Aug 04 '16 at 03:57
  • Ok thanks for this. Will go with the first one then – MadzQuestioning Aug 04 '16 at 04:08
  • @SIDU That answer's my question that when to use a third table. Thanks for this – MadzQuestioning Aug 04 '16 at 04:09
  • You haven't shown your actual schema and haven't tested anything for performance comparisons. But it would appear your first is fine. Just don't save CSV in a column table and use group_concat and find_in_set to find relationships (which you don't appear at all to be doing) – Drew Aug 04 '16 at 04:11

0 Answers0