0

Scenario :
- There are two departments namely 'Software' & 'Hardware'.
- They have divisions like 'Cobol', 'Fortran', 'Pascal', 'QBasic' in Software department.
- And 'RAM', 'HardDisk', 'Monitor', 'CPU' in Hardware department.
- Below are the table formats for both the Department and Division tables.

Database used : Oracle

Table 1 : Department Table

enter image description here

Table 2 : Division Table for the above Departments (Format 1 - Primary Key / Unique Id Column = Div_id)

enter image description here

Table 2 : Division Table for the above Departments (Format 2 - Primary Key / Unique Id Column = Div_id + Dep_id)

enter image description here

In the above two division table formats, i can't able to find the pros & cons. So in these formats, what is the best practice to create Primary / Unique ID. And why it is best?

Is there any performance difference between both formats when using select query?

Jeet
  • 1,006
  • 1
  • 14
  • 25
  • 1
    There's nothing wrong with composite keys, but in this case, it seems contrived. – reaanb Feb 19 '17 at 13:03
  • Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. – philipxy Feb 25 '18 at 18:39
  • Candidate vs composite is not a meaningful choice. Sometimes CKs are composite. We get the CKs, then pick one as PK. In your title do you mean, "when simple & composite CK options are possible"? First come tables you want. Then you determine CKs. Then you pick a PK. Given each table here & common sense for FDs, there is only one CK in each. So you have no choices. Do you mean, which design/table to choose? Well, how are divisions identified in the business? Your table 2 doesn't seem to agree with your business. – philipxy Feb 25 '18 at 18:43

2 Answers2

1

You can use one column as primary key as format 1 shows or use a combined primary key like in format 2. For me format 1 is the one to go for because:

  • every record has it's own id, and could be accessed through it by a select using just one column and you can link another table to just this field (foreign key)
  • a common implementation of a database to generate id's is the one from format 1, just a number counting up, otherwise you have to make a sequence or code this inside you application
  • format 2 has just a charm that you have for every item and division a number one and so forth, but do really care about the numbers, if you need something like that you should rather go for a separate table, and if you want to count should use count(*) rather than getMax ;-)
  • if you want to select all items from department 1 or 2 it makes no difference

so IMHO go for format 1, it's the most practical solution

Matthias Oneisz
  • 54
  • 5
  • 2
    Format 1 is simpler, not inherently more reliable. Your first two reasons can be reduced to "a single column key is simpler than composite keys". Otherwise, I basically agree with your answer. – reaanb Feb 19 '17 at 13:02
  • thanks for pointing that out, updated the answer for more convenience – Matthias Oneisz Feb 19 '17 at 19:22
0

Format 2 is close to what i'd choose. The primary key does more than act as a unique column though. It could also enforce data integrity, and help explain what the table's role is.

This article helped clear things up for me.

Abu3odeh
  • 73
  • 6