1

I am working on a project for college and stumbled upon a problem which I am not sure how to handle it in a best way.

I have a SQL Server database that is manually created, and a Winforms project in Visual Studio written in C#. The application should do CRUD operations on database.

My question is what is the best way for manipulate the primary key columns in the tables? Should I define them in database as auto increment integer and let the database management system to handle the primary keys or should I define them just as int and populate them programatically within Visual Studio project, and if so how to do it?

I am not looking for complete solution, just for hint what is the best way of doing this.

I am very much a beginner, so please be gentle...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
domac385
  • 21
  • 4
  • This really has nothing to do with Visual Studio, and more to do with database management and design. –  Aug 19 '19 at 16:41

2 Answers2

1

In general, auto-incremented (or identity or serial) primary keys are the way to go. You don't generally want your application to be worrying about things like whether the values have been used already.

If your application is multi-threaded -- that is, multiple users at the same time -- then the database will take care of any conflicts. That is quite convenient.

I am a fan of surrogate keys created by the database. In databases that cluster (sort) the rows by the primary key, it is much more efficient to have an automatically incremented value. And the database can take care of that.

There are some cases where you want a natural key. Of course, that is also permissible. But if you are going to invent a primary key for a table, let the database do the work.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thnx for the answer, but one more... https://stackoverflow.com/a/42734954/11947685 In above comment on another thread someone is pointing situation which I stumbled upon to. While designing an apllication and during the testing if it works as expected, I have primary keys inserted as 1, 2, 3, 4, 5, 1004, 1005, 1006, 2004, 3004, 3005 respectively. I didn't have so many rows in a table but it somehow populated it this way. How could I overcome this issue? – domac385 Aug 19 '19 at 16:48
  • @domac385 . . . You have not described an actual issue. Gaps are allowed in auto-incremented primary keys. That is a key performance feature that allows the database to implement the functionality and maintain performance. No issue. – Gordon Linoff Aug 19 '19 at 17:06
  • I am aware of that this is working. Let's say that I have an OCD where this is causing me pain to see it this way. I was just wondering if there is a way if I am using auto increment in the DB for priomary keys that they will be populated in consecutive order nad not by jumping for 1000 numbers beetwen two rows. – domac385 Aug 19 '19 at 17:12
  • @domac385 . . . You can use a `sequence`, but that adds a lot of overhead to the database. You shouldn't let such extraneous considerations affect how you design databases. – Gordon Linoff Aug 19 '19 at 17:15
  • 1
    Gaps in primary keys are a fact of database life. I mean no disrespect, but if gaps in the primary keys cause you pain, I think you should limit your exposure to databases. –  Aug 20 '19 at 01:23
0

When defining structures for DB backing for CRUD operations, you need to ask yourself:

Does it matter that my primary key is highly predictable?

By that I mean, if I am launching a user to a screen such "whatever.com/something/edit/1"

Aside from obvious security, does it help or harm the business process that a user can manipulate the url and inject 2 or 3 or 4 into path?

If it doesn't matter then absolutely set it as auto increminitng int on the DB side and offload that area of responsibility to the database to handle. You now no longer have to highly concern yourself with dealing with key generation.

IF it does matter then set the primary key as a unique identifier. In code when adding a new record set then you will generate a new GUID and set that as the primary key (Guid.NewGuid()). This would prevent the user from traversing your data in an uncontrolled manner as randomly guessing GUIDs would then be problematic to them. EX: New path: "whatever.com/something/edit/0f8fad5b-d9cb-469f-a165-70867728950e"

Not saying that it is impossible to stumble upon things but the regular person using your application would not be so inclined to go exploring with url manipulation since they would be wasting 99.99% of their time with invlaid posts trying to guess a valid GUID that is registered in your DB.

As an added comment, if you decided to keep the primary key as a int and not use auto-increment then you are just setting yourself up for a ton of unnecessary work where I have never personally ever seen any real return on investment for the logic you would right to check if the placeholder is already used. That and think about tracking history? You would be settings yourself up for a world of pain if you ever decided to remove records from the table and then reuse them. That is a whole other set of concerns you would have to manage on top of what you are doing.

Travis Acton
  • 4,292
  • 2
  • 18
  • 30
  • thanx for your reply... first, I am not creating a web application but a windows application, so this part about url is not part of problem domain. In databse I set the primary key columns as integer and in properties set "Is Identity" property and set increment to 1, so as I understand, primary keys are auto generated and incremented... my problem is that those auto incremented values are not consecutive but with gaps of thousand numbers in beetwen. – domac385 Aug 19 '19 at 17:15
  • That will happen if rows are deleted or you are issuing db transaction that were rolled back. Is there a business reason why it is causing concern or is this just out of curiosity? – Travis Acton Aug 19 '19 at 17:20
  • as I answered above to Gordon, this is just my OCD problem and there is no business reason for not to be this way... I just want to keep things neat and cosy, so was wondering if there is a way to accomplish that id columns (primary keys) are populated in consecutive order and not woth so big difference in auto genrated numbers... And, I didn't delete rows or had a transaction rolled back, but db still does this and I am not sure why... Just curious... – domac385 Aug 19 '19 at 17:29