I've seen this used:
Puppies
PuppyId
Puppyname
What are the advantages/disadvantages of using:
Puppies
ID
name
So, just in general:
If you assign every Puppyname its unique ID, you are able to refer to the correct Puppyname even if you had an typo in there.
Look as these books as an example, which are stored in table1:
ID Bookname Author
--------------------------------
1 Learning MySQL Adam
2 Adanced MySQL Paul
3 MySQL for Beginners Mike
BookID Bookshop Amount
------------------------
1 Chicago 3
2 New York 5
2 Dallas 1
3 Chicago 2
This tells us that there are two copies of the book "MySQL for Beginners" in the bookshop in Chicago.
If we now find the typo in table1 where it should read "Advanced MySQL" we now have just to edit this entry. The connection to table2 is not affected as it would be if we did not use an ID.
Are you saying that using PuppiesId or Puppyname would be better or worse?
Because?
You should always use an ID as a "Primary Key"; this means that its only purpose is identify this entry.
The reason people get so hung up about these sorts of things is because of how we learn (to read, play chess, or anything else): We recognise patterns. So if you use a convention which is widely accepted, you and others can 'speed-read' your code much faster.
My rules are:
There are many other things to consider, that people get very hung up on, such as underscores (first_name) vs capitalisation (firstName). If you are free to choose, then hunt around for a naming convention you like (The good thing about standards is that there are so many to choose from). You can search for phrases like like:
To answer your question directly, the disadvantage of using 'ID' instead of puppyId is that in addition to the table's own primary key, you often need to include a foreign key as one of the table's fields. For example, in the table of puppies, you may wish to have a field for 'ownerId', where you have a separate table of owners.
Shawn
You should always use an ID as a "Primary Key"; this means that its only purpose is identify this entry.
In the early 90's I took on a retail database that had extra information embedded in the product ID fields - or what has been called an "intelligent key". In this case, every product ID began with a three digit number to indicate department and class.
What a nightmare! As the product mix and merchandizing shifted, the company often created a second ID for the identical product -- and that made the total sales history unavailable. Even more, buyers were often placing new orders using either ID, pretty much at random. This had gone on for about 3 years before I got to it, and it was a nasty mess to untangle.
It can be tempting in an small database to create intelligent keys - but definitely, don't do it.
I believe that was totally uncalled for. Fischerlaender was trying to help and your question is not as clear as to make it totally obvious that you were only interested in naming conventions.
If you had asked about the pros and cons of including the table name as a prefix to the column names there would have been no ambiguity as to what you want to know.
Andreas
A case in point is Tedster's post. My take on that is that embedding extra information, such as department, in a field, would contravene textbook database design principles (3rd Normal Form, 4th Normal Form etc). But isn't it weird that it is such common practice to have a long int as the primary key, with its only purpose being to identify the entry, and yet this is not mandated by 4th Normal Form, nor taught in university courses/textbooks (although I am probably a bit out of date with the current university database course curriculum)
Shawn
The learning process only points up my lack of knowledge about how to ask the question.
I should have used the subject naming conventions.
But Shawn nailed the thrust of my question...and my elation at finally, after a couple of months trying, getting the questioned answered triggered the uncalled for response.
I have read up about and posted on various newsletters, even using naming conventions in the post and it just points up the difficult of getting on the same wavelength.
So, I promise to help in any way...when and if I get the knowledge and understanding.
no worries russgri, no harm, no foul
after a couple of months trying
too bad you didn't find us sooner ;)
