-2

I am building a db in sql server, then I will build the web app. The data has some pretty good natural key columns that I will use as the PKs. However, several of them are composite keys, which will be a bit unwieldy in the application side.

For example, a golf course can have 1, 2, or 3 courses at one location. The location has a number (32201) and each course has a name (bayou front, bayou back, bayou exec) so I would make a composite key from the number and name.

However, in the application (asp mvc) the internal routing allows for passing a single integer id from controller to view, etc for use in identifying stuff. So I am thinking of adding an non-key identity column to the Golf Course table, and others like it, so I can us the identity filed in the app. So someone can select the bayou back course on the index page of the app, and I would pass the non-key identity id number to the controller to select the details for the course from the DB and pass them to the view.

It seems at first, like I would be bypassing the purpose of the primary key by using this non-key integer, but the PK would come into play for things like referential integrity when doing updates, inserts, etc.

Thoughts?

BattlFrog
  • 3,370
  • 8
  • 56
  • 86
  • 4
    what do you mean by "internal routing allows for passing a single integer id from controller to view, etc for use in identifying stuff" ? You can pass whatever is in your viewmodel to your view, and can identify stuff with whatever logic you implement. Do you mean default route's optional id parameter? it doesn't have to be integer as well, and can be more than one. – A. Burak Erbora Feb 02 '16 at 20:11
  • Your tables should have as many keys as you need and it makes practically no difference which one becomes the primary key. If you believe you need an alternative key then the important thing is that you add a uniqueness constraint to guarantee its uniqueness. Note: IDENTITY *without* a uniqueness constraint may not necessarily be unique. – nvogel Feb 21 '16 at 13:41

1 Answers1

1

What you're talking about sounds like a surrogate key (as opposed to a natural key). There are pros and cons to using each, and both approaches have been discussed before, rather extensively, I think.

From the answer here: go ahead and use both. They are both tools, and each one should be used when it is the best tool for the job.

The only thing I'd add for your situation is that, since your tables would have both a surrogate and natural key on the same table, make sure that one (natural key or surrogate key) is the actual PK and that the other has a unique key or index on it. That way, either one can be used to uniquely identify rows in your tables.

Community
  • 1
  • 1
Captain Delano
  • 427
  • 1
  • 4
  • 12