Best Practices for Database Design
-
ISO 11179-5 is the section on naming conventions. Here's what they say about naming object classes (tables fall into this category):
6.1.1.1 Object class term
An object class term is a component of a data element name which represents an activity or object in a
context. Using a modelling methodology, as for instance an Entity Relationship Diagram (ERD) or
object model, is a way to locate and discretely place all data elements in relation to their higher-level
model entities. The attributes of entity-relationship model entities equate to data elements which are
related to each other through further application of the methodology. In an object model, data
elements are expressed as object attributes.
Models provide one kind of classification scheme for data elements. Data elements may be identified
with their related modelling entities by mapping the object class term to the model entity name.
ISO/IEC 111794 provides examples of the mapping between object class terms and ERD and object
model entities.
For example, in the data elements:
- Employee Last Name
- Cost Budget Period Total Amount
- Tree Height Measure
- Member Last Name
the components Employee, Cost, Tree, and Member are object class terms.
Reference: http://metadata-standards.org/11179/#11179-5, page 7
--
Appendix A is their example naming convention. Note: this is one possible naming convention that follows their guidelines, it isn't the only one. But, in their own words:
A.3 Lexical rules
a) Nouns are used in singular form only. Verbs (if any) are in the present tense.
Reference: http://metadata-standards.org/11179/#11179-5, page 11
--
I searched for another reference to data elements being singlular or plural and I couldn't find one that explicitely stated that they should be plural, but it's pretty clear that the guideline leans toward singular. So, implement whichever one you like. As long as you're consistent you'll be fine IAW ISO 11179.
-
Ah yes, the obligatory "flame war" of database design naming conventions.
I tend to use the KISS approach when naming database objects (Keep It Simple Stupid).
1. Table names should be relevant to the subject of the DATA the table contains.
2. Routines should be named according to the action the routine performs, and relevant to the subject of the DATA or INFORMATION the routine acts upon. In the SQL Server world, avoid using sp_ as a naming prefix.
3. Views should be named relevant to the subject of the INFORMATION it provides.
4. Follow the simple Lexical Rules. Nouns are always singular (Address instead of Addresses). Verbs are always present tense (GetAddress).
The real trick is to know the difference between what DATA is and what INFORMATION is. Most of the ISOs and Metadata standards revolve around these definitions. If you keep it simple, they're relatively easy to follow.
Personally, I find naming conventions are rather silly. I find it makes more sense just to assign common sense definitions for DATA column headings and objects. Knowing the difference between an entity, an attribute, DATA, and INFORMATION makes this task very simple. If you use common sense, the naming convention naturally occurs. Yes, in the past I have made mistakes. However the point is to learn from those mistakes so you know the right questions to ask in the future. Typically, if you have done a good job of classifying MOST of the data in the first place, and buffer users from directly accessing DATA in the table, the mistake can be corrected very easily with little or no end user impact.
-
"Personally, I find naming conventions are rather silly. I find it makes more sense just to assign common sense definitions for DATA column headings and objects."
If you're the only one who ever works on your database, then you're probably right. However, if someone else has to take over your databases in the future they'll think you were rather silly for never having used a naming convention.
Naming conventions are so other people can understand your database. Or in my case they're also for when I have to look at a database 2 years after I built it and try to remember where everything is. Common sense often is interpreted differently by different people. I would imagine that you (I'm assuming you are from the US or Western Europe here) and someone from India or Asia would use different common sense and that you'd probably have trouble understand each other's common sense names -- even assuming they were both in English.
All my database work is for clients, so I often use a different naming convention for each database (i.e. theirs). As long as it's consistent I can find things. When it's not, figuring out where things are takes at least twice as long -- even if the original author of the database used "common sense" to build it.
To each his own I suppose, but to write off all naming conventions as silly is rather naive.
-
Your idea of "common sense" will invariably be totally different from the next person you meet. Just look at dcpeterson and me in this thread. We both tend to agree on other topics, yet we can't come to a consensus on plural vs. singular table names. If we were both to work on the same database without an agreed-upon standard, it would be a mess! Half of the tables would be plural, the other singular. Standards should be defined to keep that kind of thing from happening.
--
Adam Machanic
whoisactive -
"Common sense" is far too often the rallying cry of those who are ignorant and determined to stay that way.
Despite your dismissive attitude towards naming standards, there are deeper ramifications that those who prefer to give the subject little thought will never grasp. For example the insistence of using the plural in table names can just be a preference, but it usually stems from a pretty serious misunderstanding of the Relational Model of Data.
P.S. it is not a "flame war" if the arguments are logical and civil. You might call it a debate, or a discussion, or even an exchange, but flame wars are strictly for those who cannot support their opinions with any kind of rational thought.
I will leave for others to decide on which side the statement "Personally, I find naming conventions are rather silly" falls...
-
Well, there you have it again, even ISO gets it right, although not for any of the correct reasons and not without hoplessly falling into the trap of the "First great blunder." Namely, confusing an table with an "object class" (whatever that means.) In the more widely used sense an "object" is more closely aligned with a column, while--as noted above--a table is a variable.
/*****************
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
*****************/
-
I just said that I find naming conventions to be rather silly. I never said you shouldn't have one. Neither did I say that I did not use one myself or adhere to one set forth by agreement of all database developers.
The point I was trying to make is that the naming conventions I have seen in the past are generally silly. Why in the world would you prefix a table name with tbl? Or a view name with vw? Its just silly. What is the data value of letting your user know the object type? Or stored procedures with usp_? Again, its a function. It either GETS or INSERTS or UPDATES or DELETES.
Many of the naming conventions insist on such sillyness. This is the reason why I don't like many of them. If you simply make the object name relevant to the object itself, and be consistant with it, common sense wins at the end of the day. Yes you should use a naming convention. Yes you should document it and have it agreed upon by all parties. However, it has been my experience that many of the naming conventions I have come across in my career have proven to be very intrusive and confusing.
The ISOs establish a simple framework for naming conventions. However if you read them (I have) you will find they don't tell you what convention to use. They simply give a methodology for naming objects. The convention standard is up to you (or the organization) and this is where the silliness comes into play.
-
Oh and one more thing. Relational theory is a SCIENCE not an ART.
You can't make up things like set theory. It is imperitive that any DBA understand these basic SCIENTIFIC and MATHEMATICAL skills. So my definition of "common sense" is probably different than the average joe.
Perhaps it should be reclassified as common DBA sense. Or what should be common DBA sense.
Not to be confused with "Spidey Sense".
And my comment about the "flame war" is more relative to the feeling I get every time I see one of these "naming convention" discussions, not neccesarily what is happening here. Although I'm sure some of you thought "Gee, I CAN'T BELIEVE HE'S NOT CONCERNED ALL THAT MUCH ABOUT NAMING CONVENTIONS! WHAT A MORON!". If you find yourself in that position, please see my previous post.
If you know the scientific application, and the ISO standards, it is in fact common DBA sense.
Oh, and table names should NEVER be plural.
-
Regarding "science":
A) A large percentage of DBAs don't understand -- or even care about -- the underlying foundations. Read DBDebunk.com for plenty of examples. And amongst non-database developers, it's almost a joke. None of them care. That's why "common sense" is not all that common.
B) It's an evolving science, and it's more hypothesis and theory than law -- not everyone agrees with everyone else, and every "proof" has its refutations. For instance, there are many who don't agree that D&D made the "blunder" that dcpeterson keeps insisting is somehow relevant to this discussion. Anyone who thinks this stuff is set in stone should go to the comp.databases.theory newsgroup and start asking questions. See how much consensus you get there.
--
Adam Machanic
whoisactive -
I largely agree with Jeremy's thoughts (and of course totally agree with his final conclusion) but would add a couple of things.
The Relational Model of Data IS based on science, but don't confuse the RM with normalization; they aren't really the same thing. The RM assumes some degree of normalization (1NF to be exact) but further normalization is not STRICTLY necessary in order for the scientific principles of the RM to apply.
Don't misunderstand, I'm not advocating NOT normalizing, but the RM only prescribes that you use Relations (not tables) which be definition are only Relations if they conform to the first Normal Form. The other normal forms are useful in dealing with the practical aspects of managing redundancy and integrity etc... and so, should not be ignored. However, normalization, beyond 1NF is very much an art, as it is a very subjective process. Further, normalization, like most other standards will eventually lead you into situations where you are forced to make a choice between two or more mutually exclusive priorities. See Chris Dates article at : http://www.highpoint.edu/~repprsn/Normalization.htm
The other problem with "common sense" as you have defined it, i.e. judgement that is based on a sound understanding of data management fundamentals. It ain't so common, even among those who profess to be DBA's...
I can't quite agree with you on the value of ISO in this regard. The ISO standards documents I have read clearly demonstrate that this organization is composed of people who have a poor understanding of data management fundamentals. So, even if they happen to get some of their recommendations right, it's not for the right reasons, so what kind of trust can I place in their other standards?
/*****************
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
*****************/
-
"normalization, beyond 1NF is very much an art"
Even 1NF is not universally agreed upon. For instance, take the following table, which represents a person and his or her children:
Person(s) (
)---------
Person_Id
Person_Name
Child_Name1
Child_Name2
Child_Name3
Child_Name4
Despite the obvious design flaws, such as the fact that the person cannot have more than 4 children, Date and Pascal maintain that this is NOT a violation of 1NF. Many other authors, however, feel that this is a violation -- since, among other things, this scheme introduces a forced ordering of the siblings, which is not allowed if they are a set.
In your organization, you could debate this every single time you need to design a table that might fall into this category (if you wanted to live with the design flaws) -- but it would probably be better to devise a standard, for better or for worse, so that real work can get done.
--
Adam Machanic
whoisactive -
OK, I tried, but I couldn't resist...
I totally agree with point A, but I think on B you missed the boat.
The science behind the Relational Model IS NOT evolving. Predicate Logic and Set Theory are not evolving and not likely to anytime soon. The application of those theories and people's understanding of them (and their applicability) is certainly evolving, but that's not the same thing. Perhaps the greatest struggle most of us face is that we are told that our current SQL-based products are "Relational" when they are not.
Theory and Hypothesis are not the same thing in science, even though they have come to mean much the same thing in popular usage. In fact there are very few "Laws" in science, and the number is shrinking as we discover areas in which things that were once thought of as laws do not adequately explain observed behavior.
/*****************
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
*****************/
-
I think Date, Darwen, and Pascal would only make the argument that your Person table is normalized if there were a rule that stated that a person must have 4 children and only 4. Otherwise they would object to the possibility of NULL values.
The formal definition of 1NF does NOT include any mention of "repeating groups" which is the "rule" that most would think that your example violates.
So, once again, it is people's understanding--or lack thereof--that continues to change and causes problems.
/*****************
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
*****************/
-
I didn't mean that the science BEHIND the model was evolving; I meant that the science OF the model -- i.e. the model itself, the application OF those sciences, is evolving and very much under debate.
And I didn't say, or even imply, that a theory and and a hypothesis is the same; I learned all about the scientific method in elementary school, and I know the difference. Please don't put words into my posts that weren't there.
--
Adam Machanic
whoisactive -
Viewing 15 posts - 76 through 90 (of 145 total)
You must be logged in to reply to this topic. Login to reply