Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dstarsboy
Starting Member
2 Posts |
 Posted - 2008-05-09 : 13:20:28
|
| I did a search (google and on the forums) and found a few suggestions here and there, but I'd like something more complete to follow as far as naming conventions are concerned.I wrote my first DB based on MySQL/Ruby/Active Record type naming convention...- plural table names- all lower cased- underscores between words- "id" is auto incrementer for each table- something+"_at" is for datetime fields- something+"_on" is for date fields- referencing the primary id in another table is "tablename (singular)" + "_id". This worked great in Ruby/MySQL, but in C#/SQL Server, its an ambiguity nightmare! All of my "id" fields conflict and alot of my tables have "added_at" datetime fields and they all conflict with each other. Essentially, any field that's named the same in one table as in another conflict on joins. For example: users post comments to stories submitted by users...table = articlesfield 1 = idfield 2 = titlefield 3 = bodyfield 4 = user_idtable = commentsfield 1 = idfield 2 = titlefield 3 = bodyfield 4 = user_idfield 5 = article_idTrying to join these two tables is an ambiguity nightmare but I'd like to not have to name every field uniquely or start adding table prefixes to them all...I guess I just need some good suggestions or links to recommended table structure/naming conventions for SQL Server. Thanks in advance! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-09 : 13:26:38
|
| Thats not a problem when u provide alaises for tables and fully qualify all your column names. For example the above join b/w tables can be written as SELECT a.id,a.title,a.body,a.user_id,c.id,c.title,...FROM articles aINNER JOIN comments cON a.user_id=c.user_idNotice a & c in query above and also aliases in columns. This will remove ambiguity and avoid errors. |
 |
|
|
dstarsboy
Starting Member
2 Posts |
Posted - 2008-05-09 : 13:39:40
|
| You're right, that's how I had to write my queries in order for it to work with my current table structures.So if I need all of the fields in these two tables it ends up looking like this... (sorry, I wrote "story_id" as the link, when it should be "article_id" in my example)SELECT a.title AS a_title, a.body AS a_body, a.user_id AS a_user_id,c.title AS c_title, c.body AS c_body, c.user_id AS c_user_id, article_id, c.id AS c_id FROM articles a LEFT JOIN comments c ON c.article_id = a.id;which is pretty darn ugly, so I believe that my table naming conventions are really not optimal, which is why I'd like suggestions I guess. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-05-09 : 14:03:30
|
| With few exceptions, about the only thing that should inherit the name of the table is the ID. So, in your example you could have ArticlesID and CommentsID. I also prefer singular table names.However, you could make a case for including the entity name in the column name if you have lots of entities with the same attributes. Although, from a purist perspective, this wouldn’t be considered good form. :) |
|
|
|
|
|
|
|
|
Recommended naming convention for tables/columns