0

I have a table with products like

Products

id
name
styles_id

I also have a table with styles like

Styles

id
name

When I add a style to a product I use a foreign key id value from the Styles table. I do this for a few years now on a lot of projects but I was thinking WHY? It would be a lot easier to get the products if I have a Styles table with a name as primary key and without an id. So I do not have to do a join to get the style name of the product. I see it is done a lot but what are the advantages of the id in the Styles table?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sven van den Boogaart
  • 11,833
  • 21
  • 86
  • 169
  • Also a duplicate of http://stackoverflow.com/q/1229173/458741, http://stackoverflow.com/q/3747730/458741, http://stackoverflow.com/q/590442/458741, etc... this is one of the older "wars" in DB design... now you know the keywords to look for there's a lot of information out there, not just on Stack Overflow. – Ben May 17 '14 at 13:43
  • Thank you the links helped alot, but what would you suggest in this situation ? I think the only disadvantage of no id is if i want to change the style name but i could easily add ON UPDATE CASCADE .. – Sven van den Boogaart May 17 '14 at 13:50
  • 1
    I don't really see how your suggestion is different to the links @Sven. If you feel like you want a natural key now the general suggestion is to use a surrogate key but add a unique not null constraint on the name. That way, you can change your mind in the future without having to alter _all_ your code. – Ben May 17 '14 at 13:51

1 Answers1

2

The main advantage is that you can change the name of a product without affecting the references.

You could add multiple names, if, for instance, you wanted to internationalize your database.

Almost every time that I fail to put an auto incremented id in a table, I regret it as some point. That might be when duplicates appear in the table and I want to delete them. That might be when the structure changes in an unexpected way, such as internationalization. That might be when I want to know the last thing that was entered -- auto-incremented id provides that information. Now, I put such an id in almost every table that I create, even in databases where it is painful to do, such as most versions of Oracle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    +1 - also, using a **numerical** `id` is (a) faster on joins, and (b) not prone to misspellings and uppercase/lowercase issues and more hassle from using strings as identifiers. – marc_s May 17 '14 at 15:12