1

I have a table called branch

It looks something like.

+----------------+--------------+
|     branch_id  |  branch_name |
+----------------+--------------+
|         1      |  TestBranch1 |
|         2      |  TestBranch2 |
+----------------+--------------+

I've set the branch_id as primary key.

Now my question is related to the next table called item

It looks like this.

+----------------+-----------+---------------------------+
|     branch_id  |  item_id  |         item_name         |
+----------------+-----------+---------------------------+
|         1      |     1     |  Apple                    |
|         1      |     2     |  Ball                     |
|         2      |     1     |  Totally Difference Apple |
|         2      |     2     |  Apple Apple 2            |
+----------------+-----------+---------------------------+

I'd like to know if I need to create a primary key for my item table?

UPDATE They do not share the same items. Sorry for the confusion.. A branch can create a product that doesn't exist in the other branch. They are like two stores sharing the same database.

UPDATE Sorry for the incomplete information. These tables are actually from two local database... I'm trying to create a database that can exist on its own but would still have no problem when mixed with another. So the system would just append all the item data from another branch without mixing them up.. The branches doesn't take the item_id of the other branches in consideration when generating a unique_id for their items. All the databases however may share same branch table as reference.

Thank you guys in advance.

dachi
  • 1,604
  • 11
  • 15
Philip Badilla
  • 1,038
  • 1
  • 10
  • 21

3 Answers3

3

I'd like to know if I need to create a primary key for my item table?

You always1 need a key2, whether the table is involved in a relationship3 or not. The only question is what kind of key?

Here are your options in this case:

  1. Make {item_id} alone a key. This makes the relationship "non-identifying" and item a "strong" entity...
    • Which produces a slimmer key (compared to the second option), therefore any child tables that may reference it are slimmer.
    • Any ON UPDATE CASCADE actions are cut-off at the level of the item and not propagated to children.
    • May play better with ORMs.
  2. Make a composite4 key on {branch_id, item_no}. This makes the relationship "identifying" and item a "weak" entity...
    • Which makes item itself slimmer (one less index).
    • Which may be very useful for clustering.
    • May help you avoid a JOIN in some cases (if there are child tables, branch_id is propagated to them).
    • May be necessary for correctly modelling "diamond-shaped" dependencies.

So pick your poison ;)

Of course, branch_id is a foreign key (but not key) in both cases.

And orthogonal to all that, if item_name has to be unique per-branch (as opposed to per whole table), you need a composite key on {branch_id, item_name} as well.

1 From the logical perspective, you always need a key, otherwise your table would be a multiset, therefore not a relation (which is a set), therefore your database would no longer be "relational". From the physical perspective, there may be some special cases for breaking this rule, but they are rare.

2 Whether its primary or not is immaterial from the logical standpoint, although it may be important if the DBMS ascribes a special meaning to it, such is the case with InnoDB which uses primary key as clustering key.

3 Please make a distinction between "relation" and "relationship".

4 Aka. "compound".

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • wow. this really make sense. well actually my database set up is quite unique. I'm trying to pull off a database that can serve a single branch but would have no problem merging two branches that existed separately before. So they could have the same item_ids for their items but I could guarantee that they have different branch_ids because these are made unique before installation. with this set up. I could create local servers and consolidate their data on 1 corporate server without the hassle. Just uploading and updating data. – Philip Badilla Mar 04 '14 at 12:32
0

According to your example data you are using n to m relations and not 1 to m. It should be like this

item table
----------
item_id  | item_name
    1    | Apple
    2    | Ball


branch_item table
-----------------
item_id  | branch_id
    1    | 1
    1    | 2
    2    | 1
    2    | 2

And your brach_item table should have a compound unique key containg branch_id and item_id to make sure no duplicate entries can be added.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 1
    It would be nice if you also explained why and not just present the solution. He makes it then right this time, but not with the next tables where the situation might be the same. – fancyPants Mar 04 '14 at 11:07
  • @juergen d, I had one doubt, if I should be create the table without pk look like as table, http://a4academics.com/interview-questions/53-database-and-sql/397-top-100-database-sql-interview-questions-and-answers-examples-queries – jmail Mar 04 '14 at 11:57
0

Yes you do. The Primary key is what allows the many to one relationship to exist.

This requirement is already catered for by the branch_id column.

The item_id column is not required for the one-to-many relationship in your example.

WonderWorker
  • 8,539
  • 4
  • 63
  • 74
  • As I have already explained in my update. They do not share the same item. So it's not a many to many relationship.. It's just 1 branch to many items. Each branch maintain a separate list of items. No branch shares an item. – Philip Badilla Mar 04 '14 at 11:41
  • Understood. I have amended my answer. Just so that you know, in your example item_id does not make sense. On the surface it looks like a primary key, except that each item does not have a unique number. If you changed the item_id column from 1,2,1 and 2 to 1,2,3 and 4 then it would make sense. – WonderWorker Mar 04 '14 at 11:43
  • The thing is. this data would actually exist in different branches and the consolidated data would look like this. – Philip Badilla Mar 04 '14 at 12:13