Im designing a database and find that I seem to have a few small tables with say, 3 or 4 fields, where all of those fields are required to identify a record. Is it better to make a composite primary key consisting of all the fields, or introduce a non-composite primary key, like an auto_increment integer?
Generally, I like to have an auto-increment integer field as the primary key, mainly to simplify relations between other tables. If it is a logical requirement that a combination of the other data fields must be unique, then you can still assign a composite unique index on those fields. (Hope that helps -- and hope I understood your question. )
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Proper design technique is to have a primary key set on columns that would make your result unique. You call it "composite unique index".
Autoincrement is not a good design practice.
Proper design technique is to have a primary key set on columns that would make your result unique. You call it "composite unique index".
Autoincrement is not a good design practice.
Not necessarily true. suppose u need foreign keys to point at such a composite key. this is exactly proper use of auto-incremement type fields. also, simplifies client code.
it is true though that the natural, or "business primary key" should be defined by business rules and reflected in the db, there are certainly valid uses for identity (auto-incrementing) fields.
A foreign key would point to only one of the fields in the composite key. I don't see where the problem is russel. Can you clarify?
From my understanding, an auto increment has no real meaning to it. If you reference a foreign key to an auto increment key, and the value of that key is 4, 4 does not tell you much.
But if it was referencing something meaningful, such as C10001, then it make more sense that that's the customer number (bad example, but i think you know what i mean). In my oracle class we're told to stay away from them, so I would love to get more insight on this subject.
Database design and teaching must have changed since I went to Uni if they are telling you to stay away from auto-increment primary keys. If you create a database application that lets the users enter in customer information, would you expect the user to know what id to use for a customer they enter into the DB? If they have thousands of customers how would they know a unique number to identify the next customer? As Russell said "business primary keys" need to be apart of the business rules. I certainly use auto-increment in my main entity tables and a composite primary key for my associate tables.
JayM in Oracle you create a sequence and enter the next.sequence number into the DB, that is the same as auto-increment, unless it has changed since oracle 9i
Last edited by aussie girl; 11-24-2006 at 11:09 PM.
A foreign key would point to only one of the fields in the composite key. I don't see where the problem is russel. Can you clarify?
This isn't necessarily true: if logical primary key is composite, then none of individual fields are unique. That most likely means any table referencing this key will have to contain same set of fields.
Originally Posted by JayM
From my understanding, an auto increment has no real meaning to it. If you reference a foreign key to an auto increment key, and the value of that key is 4, 4 does not tell you much.
It doesn't need to tell you anything. You know the relationship, you can select actual data...
Originally Posted by JayM
But if it was referencing something meaningful, such as C10001, then it make more sense that that's the customer number (bad example, but i think you know what i mean). In my oracle class we're told to stay away from them, so I would love to get more insight on this subject.
Err.. Whomever taught that class needs to be taken out and shot, at least if
your relayed this info correctly.
Yes, when you design your logical, fully normalized, structure, you need to understand what actual defining unique data is, but when you get down to actual schema, you need to make it actually work. This, among other things, means you really don't want to have multi-column joins, and things like that.
From my understanding, an auto increment has no real meaning to it. If you reference a foreign key to an auto increment key, and the value of that key is 4, 4 does not tell you much.
this is what joins are for. suppose your table has
id name
1 JayM
2 russell
u are right that 1 means nothing, but we can select name by joining on id.
there are other valid uses of auto-incrementing fields as well. how about purchase order numbers, sales orders, customer IDs...
Makes sense. But customer order numbes, cust ids and those other fields should be generated through some sort of script. I would not expect a customer to enter something like that. If you look at large websites, no site has cust id 1, or cust id 99. Hence they have some sort of script generating a unique number, and inserting it as a unique key in the cust id table.
Iluxa, it's not about whether your not YOU know the relationship, it's about the next maintenance programmer thats going to come after you. Also, C10001 would work pefectly fine in a schema, so I don't see a problem there.
Great discussion guys/girls. Sorry for the original poster, I sort of hijacked this uknowingly.
I just think that there's a better way to design a table than a auto-number/next sequence. It might be fine initially, but perhaps it would complicate maintenance down the line. I mean, if you can reference real data, why create an extra field that is somewhat meaningless? Perhaps different rules apply to the business world?
Makes sense. But customer order numbes, cust ids and those other fields should be generated through some sort of script. I would not expect a customer to enter something like that. If you look at large websites, no site has cust id 1, or cust id 99. Hence they have some sort of script generating a unique number, and inserting it as a unique key in the cust id table.
Well
A. Some sites do have purely numeric order IDs. They might prepend 0's to
them to make entering them harder, but still
B. If you want customer to enter alphanumeric string that makes as little sense
as numeric does, but is harder to type, since it's all over keyboard, instead of just
numeric pad, nothing prevents you from generating them, and storing alongside
with actual ID.
Originally Posted by JayM
Iluxa, it's not about whether your not YOU know the relationship, it's about the next maintenance programmer thats going to come after you. Also, C10001 would work pefectly fine in a schema, so I don't see a problem there.
Next maintenance programmer will inherit your database ERD, that you already
drew and that is hanging on the wall of your cubicle/office/whatever, ant that
is stored in your SCM system, right?
He should be able to figure things out from there...
Originally Posted by JayM
I just think that there's a better way to design a table than a auto-number/next sequence. It might be fine initially, but perhaps it would complicate maintenance down the line. I mean, if you can reference real data, why create an extra field that is somewhat meaningless? Perhaps different rules apply to the business world?
One word: performance.
Also logic, of course. If you try to encode extra relationship info into data
(i.e. by saying "All customer IDs should start with 'C'") and then your program
will rely on this somehow, database engine will not know, and will not be able
to enforce these constraints. Neither will your next maintenance programmer
know about it until he breaks the rule, and spends two days looking for the code
that was expecting your data to be in some specific format.
Reply With Quote
)
Originally Posted by CCCP
Bookmarks