> Here are a few general db standards: > http://livedocs.macromedia.com/wtg/public/coding_standards > /database.html
I agree with some of these but not all of them... My list: 1) singular table names - agreed (though I'm not too disturbed if I find one that's not) 2) descriptive primary keys matching a convention, preverably "tablenameid" -- agreed (I'm horribly disturbed if I find one that's not) 3) descriptive column names in general - agreed - (it seems like it's often tough to get people to agree on what constitutes descriptive unfortunately) 4) table names repeated in column names - yes and no - generally speaking I think that including the table name as a prefix in a column name is useful as I've frequently run into situations where the column name would otherwise appear in multiple tables in a view or ad-hoc query and prefixing the column with the table name eliminates the need to alias the column in the view or query - the tables can simply retain their existing column names 5) primary and foreign key column names - yes and no - a standardized naming convention can be helpful, although I don't consider it mandatory - I rarely access primary or foreign keys except in a very automated way, in which case the name is irrespective of the task - though if I were going to specify a naming convention for foreign keys, I wouldn't use a sequential number as part of the foreign key name, primarily because it takes the place of less arbitrary information (the name of the alternate table) but also because the number itself is just meaningless and prone to becoming "jigsawed" later -- that is, you create 3 foreign keys then later decide that the column associated with foreign key 2 isn't necessary so you now have fk1 and fk3 but not fk2 -- or you have to renumber your fk's... blech... 6) no surrogate keys in many-to-many relationship tables unless additional information is included in the relationship data - agreed - additionally, all tables must have a single-column primary key unless they are many-to-many relationship tables (and in most many-to-many relationship tables I would apply a multi-column primary key including all relevant foreign keys in the table) 7) related columns grouped together - isn't this more of a feature of the tool used to display the database than of the database itself? I tend to use inverted column names so that an alphabetical sort of the columns will produce this grouping automatically, so my guideline would probably be "namefirst" and "namelast" not "firstname" and "lastname". 8) created / updated columns in all tables -- agreed -- I'd only ammend this to say in all tables which have a primary key (see surrogate keys) or possibly in all tables which are not many-to-many relationship tables. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200591 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
