2

Sometimes, there are certain tables in an application with only one column in each of them. Data of records within the respective columns are unique. Examples are: a table for country names, a table for product names (up to 60 characters long, say), a table for company codes (3 characters long and determined by the user), a table for address types (say, billing, delivery), etc.

For tables like these, as the records are unique and not null, the only column can be used as the primary key, technically speaking.

So my question is, is it good enough to use that column as the primary key for the table? Or, is it still desirable to add another column (country_id, product_id, company_id, addresstype_id) as the primary key for the table? Why?

Thanks in advance for any advice.

melpomene
  • 84,125
  • 8
  • 85
  • 148
kennethw
  • 75
  • 1
  • 8
  • 2
    If a product name ever changes, that's much easier to do if everyone else refers to the product by ID, not name. – melpomene Feb 16 '19 at 12:06
  • 3
    A country name can be quite long, using a country_code greatly reduces space usage (and there's an international standard, too) – dnoeth Feb 16 '19 at 12:12
  • 3
    There could be homonyms(Paris, Texas <--> Paris, France), and there can be (temporal) renamings (Leningrad <--> st Petersburg). A surrogate key can reduce the cost of updating these. – wildplasser Feb 16 '19 at 12:30
  • I will most like have to change some tables, like the ones for country, product. Thanks all for the help. – kennethw Feb 16 '19 at 16:44

1 Answers1

2

there is always a debate between using surrogate keys and composite keys as primary key. using composite primary keys always introduces some complexity to your database design so to your application.

think that you have another table which is needed to have direct relationship between your resulting table (billing table). For the composite key scenario you need to have 4 columns in your related table in order to connect with the billing table. On the other hand, if you use surrogate keys, you will have one identity column (simplicity) and you can create unique constraint on (country_id, product_id, company_id, addresstype_id)

but it is hard to say this approach is better then the other one because they both have Pros and Cons.

You can check This for more information

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
  • Thanks. It looks that I will modfify the setup of the tables. Surogate keys appear to be a better choice for certain scenarios. I am trying to buid a database for reording customers' orders, our purchase orders, sales invoices, packing lists. – kennethw Feb 16 '19 at 16:30
  • Good luck mate. If you feel that this answer is helpful for you, please consider to upvote and/or mark it as answer so the community can also benefit from it. – Derviş Kayımbaşıoğlu Feb 16 '19 at 16:34
  • @kennethw & Simonare It's not surrogate vs composite here, it's surrogate vs natural. Of course, composite is a case of natural. – philipxy Feb 16 '19 at 19:43