Best Practices for Database Design
-
As far as naming conventions go, I have one thing to say. BE CONSISTENT.
cl
Signature is NULL
-
Just to add some conventions I tend to follow..
User-created stored procedures are named usp_xxxxxx (this is a commonly used standard, but after reading this thread, clearly it's not used everywhere).
I use lowercase names with lots of underscores in all types of object names. This is mainly because of one application I use that shows all table/column names in upper case. If I tried using mixed case or camel notation, it would be very difficult to read in the application. I also think that SQL written with upper case keywords and lower case object names looks very clean and easy to read. Here's a brief example:
SELECT t.bob_id, t.bob_name
FROM dbo.my_table t
JOIN dbo.my_other_table t2 ON (t.my_id = t2.my_id)
WHERE t.bob_name = 'bob'
For those who can type, typing an underscore is no more difficult than using a capital letter. Incidently, when I code I use mixed case (camel) rather than underscores, so I see the value in both methods.
Consistency is more important than choosing a particular naming standard though.
I've looked at ISO-11179 and although it's an international standard, I find that dropping all prefixes/suffixes in my object names (or programming code as it applies there as well) makes coding more difficult because I'm constantly trying to remember is gender a code or a full description? Or was joe_shoe numeric or alpha-numeric? I also don't like the plural table names. Yes, tables have multiple rows so I can see why some do it, but when you then query on contacts.first_name it "feels weird" to me. I'd rather see contact.first_name. I'm also a bit lazy and that extra "s" really adds up!
Maybe it only takes more practice to get used to it, but I've elected to not follow ISO-11179 for now and it appears I'm in the vast majority. -
Good article. Naming conventions can get very religious, but I have to agree with some others that the underscore (_) character should be avoided as much as possible. This seems to be a holdover from Oracle (and other Dbs?) where everything in the schema was in upper case. Underscores require a "shift-little finger" operation which is one of the weakest for many typists. From a productivity standpoint, it is much easier to type RefTable rather than ref_table.
You're to be commended for putting your suggestions out for others to use and comment on.
Thanks!
-
I, for one don't care what the iso standard says, they are wrong. A table does not represent a set of entities. Tables represent a single predicate function. So the Employee table with the following attributes:
EmpNo, FirstName, LastName, DeptNo, Address,...
MEANS literally "Employee (EmpNo) has a first name of (FirstName), a last name of (LastName), works in (DeptNo), and lives at (Address),...
A single relation must have one, and only one predicate as its meaning. Furthermore a relation is just the definition of the table and has no "rows" or "entities." The definition of the thing that combines a relation AND has data in it is actually a variable, hence the term Relation Variable or relvar for short. The set of tuples in a relvar are a single value regardless of whether a relvar contains 1 or a million "rows." Any modification to any tuple in the relvar is logically changing the entire value of that variable, just like any other variable.
Therefor "Tables" should, regardless of what ISO says, be named in the singular.
It is amazing to me how many people can't grasp the simple relationship between a relation and its value but have no problem declaring variables and dealing with their changing values seperately... This is what Chris Date termed the "First great blunder" of SQL, and it has ramifications far beyond this discussion.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
dcpeterson:
Every tuple is itself a relation. This concept is called "relational closure," and it's very important -- this closure is the reason that I've noted elsewhere in the thread that, e.g., tables and views are equivalent.
But what we need to realize is that a table, while it is a "single" relation in and of itself, is a "collection" (set) of relations as well (every tuple being a relation). And the predicate has meaning ONLY for a single tuple, not for the entire collection. We must remember that the table is a container, and not get tied up in thinking that the table is actually a single entity.
--
Adam Machanic
whoisactive -
"Every tuple is itself a relation. This concept is called "relational closure," and it's very important -- this closure is the reason that I've noted elsewhere in the thread that, e.g., tables and views are equivalent."
...Exactly, hence I wrote to support your thoughts about treating views (virtual relvars) the same way we treat base relvars.
However, a set of tuples is a singular thing. Math (the basis of relational theory) treats sets as a single "thing." The tuples contained in the relvar do not define the relation (note the careful use of the proper terms here) rather the relation defines the MEANING of the relvar. Since data modeling is entirely semantics (defining what things mean) I chose to use the relation rather than the relvar as my point of reference. The number of tuples in a relvar is totally irrelevant (i.e. your example of a "singleton" relvar), the definition of the relation is what is important and what, in my opinion should be reflected in the name.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Yes, a set is itself a singular, but there are some important problems with being so strict in our definitions of objects:
One of the tuples within the set of tuples is not equal to the set of tuples. Naming a set of employees 'Employee' indicates (to a user's subconcious mind) that each member IS the set. Most users do not think well in terms of sets. Using plurals reminds them that they're dealing with more than one entity.
... and finally, anyone else think of "The Klumps" every time we get into this topic?
--
Adam Machanic
whoisactive -
You know I love this
Customers.FirstNames???? NO
Customer.FirstName
The table is an abstract concept (presented in the form of an object or container). It is the container of the concept of multiple objects with related features.
Think of it this way.
Walk into a room where a party is taking place and what do you see, lot's of various objects. '
Now among those objects are all the people in attendance or the attendees or whatever word you want to use to describe them.
Now among each of those individual related objects they all have First Names and Last Names and such.
So in creating a database of this data you would look at it this way
Party (Database) -> Attendees (Table) -> First_Name (Each attendees first name)
Now if I want to talk to Jon Franklin I pull his name out of the attendees and commuincate with him (a WHERE clause).
But the host walks in and want's every ones attention (SELECT * FROM attendees) '
But suppose you want to talk to all Emily's in the room (SELECT * FROM attendees WHERE attendees.FirstName = 'Emily').
Even thou attendees.FirstName may sound wrong to you it isn't you have to stop thinking about attendees at all for the query, all attendees keeps you from doing is being misunderstood about who's first name you want to talk about such as the case of the wait staff (Wait_Staff) at a party.
And before anyone slams me I prefer underscores so that when anybody using any tool looks at my data there is absolutely no confusion.
-
Here you are confusing the issues again. I am not naming the set of employees "employee" I am naming the variable that contains the set "employee"
Nobody would argue that the naming of a variable actually affects the value or even less that the value of the variable should be reflected in its name, although it should describe what it is. That is exactly what you proposed when you suggest that a singleton table could use the singular name.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Why do you think we have variable names at all?
They're for humans, to understand what the variable represents. At the end of the day, two groups need to use these names: computers and humans. Computers don't care, and humans need all the help we can get. And most humans don't care if a relvar, being a set, is a single thing. They care that the Employees table contains data about employees -- and most of the time they need to be reminded that said table is a SET of employees, not a single employee.
--
Adam Machanic
whoisactive -
To further amplify my point, your concern about confusion is, I think, somewhat overstated. People are used to dealing with singular names for containers of multiple objects:
Train Station rather than Trains Station
Shoe Store rather than Shoes Store
Container Ship rather than Containers Ship
I could go on...
No one thinks that since Train is singular that there is on one train, that they can't buy more than one pair of shoes (unless you live under the socialist boot!), or that a ship carries only one container...
And no, I don't think of the Klumps, since I haven't seen the movie.
But I do enjoy having these kinds of conversations with intelligent people
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
To give us some kind of idea of what the variable means and how it should be used, but not (except in the loosest sense of the word) to convey the actual value contained therein.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
That's totally different -- a Train Station is a lot more than just a bunch of trains. A train station implies a physical building that may or may not have some trains at any given time, in addition to the building itself, ticket takers, really nasty cheap fast food, and very dirty bathrooms.
A set of trains, on the other hand, is nothing more than a set of trains. And a relvar represents a set of entities; again, you're getting caught up thinking about the container for the set, rather than the set itself.
--
Adam Machanic
whoisactive -
"you're getting caught up thinking about the container for the set, rather than the set itself."
Guilty as charged! Since it is the container (relation) that imparts meaning to the set I think the relation is where the point of focus should be when naming your tables.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Regarding the "name" or "description" field. Let's say you have a field in 2 tables named "name". The tables are named item and customer. I have found it much easier to name the fields accordingly. I name those fields CustomerName and ItemName. I find it confusing to have the same column name for unrelated fields throughout a database.
Also, does anybody have a valid justification for naming a foreign key field other than the primay key field name? E.g CustomerID is my primary key, in the order table why in the world would I name it FK_CustomerID? There is nothing more fun (*gritting teeth*) than trying to find these types of relationships.
Dan Avsec
MCSD, MSDBA, MCT
Viewing 15 posts - 31 through 45 (of 145 total)
You must be logged in to reply to this topic. Login to reply