I’ve been on a run about coding standards lately, reading about them, figuring out which to follow and which to not follow. Well one thing I had not been looking at much was database standards. I realized I had a bunch of questions; how do you name tables, according to what schema, are views concatenated names correlated to their respective tables, do stored procedures have verb noun correlation, do functions get named similar to stored procedures, do we prefix database objects with shortened abbreviations and such related to what they are? Well I’ve started keeping a list of things I’m starting to prefer when designing and building a database. Enjoy…
Database Standards :: Build 0.01
- Table Names should be named to a singular of the data object they hold. At least when utilizing a domain or business object pattern approach to development, for readability of generated code (yeah, a standard based on generated code!) tables should be named singular. Examples would include; Car, Bike, Train, or if one had more granular break downs Wheel, WheelType, WheelTire.
- Database objects should use casing just like code objects. So if there is a Car Object that has Wheel Objects and a Wheel Type Structure the tables in the database should be named accordingly with the same casing; Car, Wheel, WheelType.
- Artificial name spaces should be setup for database objects. If a table is directly related to a particular table, such as a Wheel having a type in the WheelType and a tire type in the WheelTire table, each should be prefixed as written here with Wheel. This assures easy visual coordination of tables that are closely related via domain logic, business logic, and via referential relationships within the database itself.
- If one goes with the base table pattern (similar to using base classes) and sets up a base table to hold vehicle information the base table should be post-fixed or pre-fixed to the associated tables. Thus we would have VehicleCar, VehicleTrain, or VehicleBike based off of the Vehicle base table. The reverse would look like CarVehicle, TrainVehicle, and BikeVehicle. I prefer the fore-mentioned naming convention but the later convention is more readable in my opinion.
So far, that’s what I got. If I get to deal more with database standards I’ll definitely post them here. Anyone else have ideas, notions, arguments, or opinions on what should change, be added, or deleted? Submit a comment.
How do you feel about adding an additional layer to obscure the database by using views that correspond to each table?
I’d like to think of the layer of views as a "clarification" layer 🙂 hehee
But yeah, it is definitely needed sometimes. Sometimes the individual software patterns, data requirements, or other issues force the use of views.
I however must also state that if it is NOT needed, such as an internal type specific database, that for instance tracks reporting data, there is no reason to use views in simple or even mid-size databases.
Generally I’ve also found views to increase the overhead sometimes when it is not needed. Sometimes one must step away from a view and just create a real time table.
But I digress, I could go on for hours listing the exceptions to the rule.
…and I forgot to mention in previous. Thanks for the input John, I’ll have to delineate those differences for view use or non-use and write up some standards on those too.