0
$\begingroup$

Till now, I always gave each db-table an id-column (generally with the table name and an underscore as a prefix, e.g. employee_id), and I also never have seen another approach to unique a db-table-record in practice. But on the other hand during my studies, I often have seen other columns that act as a primary key, for instance: an email/url as priamary key for a table user/photo.

At the moment I'm designing a database that contains a table first_name (because a human can have more than one first name). The scheme of this table first_name:{[first_name_id:integer,first_name:string]}. But to avoid duplicate, I would like to use the first_name as a primary key. And I also have a table employee with the column email address that also can be used as a primary key, becuase its unique.

My loss is that I dont know if I should use this approach, becuase its uncommon in practice and maybe it will cause any trouble during the programming part. So my questions is: An ID (auto increment integer) always as primary key for each db-table?

$\endgroup$

closed as off-topic by David Richerby, vonbrand, Luke Mathieson, hengxin, Juho Feb 27 '16 at 13:57

This question appears to be off-topic. The users who voted to close gave these specific reasons:

  • "This question does not appear to be about computer science, within the scope defined in the help center." – Luke Mathieson, Juho
  • "Questions about software development or programming tools are off-topic here, but can be asked on Stack Overflow." – David Richerby, vonbrand, hengxin
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    $\begingroup$ This seems to be essentially a coding question: how should I implement this database. Coding questions are off-topic, here. $\endgroup$ – David Richerby Feb 17 '16 at 21:01
0
$\begingroup$

I assume you are using RDBS.As far as you can set any value as primary key this will not cause strict programming trouble - but will be cumbersome.

What about convention that people use integer?
This comes from two parts:
It is easier to give unique id from autoincremented value.
Second is from relations and optimizations - database internally connects related items, so setting something different will cause overhead.

You should read two pages:
normal forms in database and primary keys.

Normal form is something you should have (at least third level) in your database, it saves programming trouble later like with splitting keys manually.

$\endgroup$
  • $\begingroup$ Indeed cumbersome is a point that I noticed, but that the id as an optimization purpose is new for me. Thank you! $\endgroup$ – software knife Feb 18 '16 at 7:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.