AutoNumber VS Number
Language: DataBase.
Program: Microsoft Access.
Question: When creating new tables. What is a good practice to use
AutoNumber and what is a good practice to use Number? In other words, when is it better to use which? I know the differences but I am just not sure how that would effect implementation when using a programming language like C# to read/write in/to the database.
Feel free to go in as many details and view this in different angles if you like to. The more info I can get the better.
Thank you.7 Replies
Here is an example of my case:
Let's say, for simplicity, we have a table named
Animal with the fields:
. ID is the primary key.
. ID doesn't have a real meaning to the animal, it is just a field for uniqueness of row.
. Rest of the fields are not required and may be empty.
Now let's say we are using a programming language to insert a new row. (In my case it will be C#)
The only information we know is the weight of the animal.
In our query, this can be done in one of these methods:
1- Insert only weight (MS Access will automatically fill the rest)
2- Insert all data, put NULLs explicitly to where it is not specified.
Here is the thing, if you choose Number/AutoNumber, you may have to insert all data, including null ones, like method 2. This is a bit of an issue for performance.Well, I think you'd want the ID to always be defined, so AutoNumber is better since you don't have to specify what should the newest number be
However, AutoNumber would leave "gaps" if you were to delete a row from the table, so you'd have unused IDs after a while
Of course, this only applies if your deletion is destructive (i.e. you purge the row from the table instead of marking it as deleted)
With Number, you have a more fine grained access on how to use it, but it is somewhat cumbersome because you'd essentially have to write a maybe-expensive algorithm that detects the first unused ID
Let's say that we have rows with IDs 1, 2, 3, 4, 8, 10, 19. Figuring out that the next ID we have to use is 5 will incurr some performance cost
If you don't care about gaps in your ID usage, just use AutoNumber
If you want to use all the IDs available, then use Number and be diligent that you are always using the proper ID by writing some code
I have already done this before, it is as simple as finding the last number and then increment it by 1 to find a new ID. This just replicates the autonumber behavior in that context.
Yeah that is a bit concerning, what if we just ignore missing parts tho?
nvm here is the answer
Yeah
ok I think I understand your point
I will be using AutoNumber for primary IDs.
A bit of a different topic, but do you happen to know a decent C# library to make SQL queries connecting to my database?
Well, it depends on what you want to do. If you want to write the SQL queries yourself, I think the Microsoft libraries do a pretty good job
I only have used the Entity Framework, and they probably have an adapter for a MS Access database
Yeah I am using OleDb