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 |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2007-07-04 : 22:53:29
|
| "What's in a name? That which we call a roseBy any other name would smell as sweet."Ok all joking aside, few questions on how to BOTH use proper names for my tables and sprocs AND a way to trim the # of sprocs.1. TablesCustomersSalesNow if I have a table that contains only the primary keys of the customers and sales table (maybe some other columns), how should I name the table?Customers_SalesorrelCustomersSalesor??2. On each table I will need a basic set of sprocs. For example, the Customers table will need:i. A sproc to 'SELECT' from the tableii. A sproc to 'DELETE' a customer by the customeridiii.A sproc to 'UPDATE' a customer rowiv. A sproc to 'INSERT' a row into the customers table.I usually do this:Customers_GetCustomers_InsertCustomers_DeleteCustomers_UpdateNow if I have 100 tables in my database, that means I will have at least 400 sprocs to manage!Do you guys have any creative ways of trimming the # down?I was thinking this:Use only 1 sproc, but have a extra parameter that tells the sproc what action to take:So the inside of the sproc will look like:CREATE PROCEDURE XXXXXX(@....@....@Action INT)IF(@Action = 1) -- SELECT..IF(@action = 2) -- DELETE... and so on.Good idea? Or have a better idea? |
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-05 : 03:16:03
|
quote:
Originally posted by sql777[brNow if I have 100 tables in my database, that means I will have at least 400 sprocs to manage!
I wouldn't get hung up on that. The actual code will be broadly the same whether you have 400 sprocs or 100 sprocs that accept one of four parameter values. The 400 will be ever so slightly easier to debug too.I will quite often use a single upsert sproc rather than an insert and an update but that is dependent on business requirements and how you want to handle things.HTH |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-05 : 03:17:57
|
BTW - prepare for some controversy. Whether or not to allow "_" in object names or exclusively use camelCase is enough to make DBAs upset enough to not speak to each other for a week  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-05 : 05:36:51
|
| See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Good+table+naming+convention"Now if I have 100 tables in my database, that means I will have at least 400 sprocs to manage!"We mechanically generate these CRUD Sprocs, and then only change them by exception.EDIT: When the Schema of a table changes we re-generate the CRUD to a Temp File and Compare against the actual, and then merge in any appropriate changes - i.e. we can retain any bespoke code that has been added."if I have a table that contains only the primary keys of the customers and sales table"We normally add the word "Link" to the table name - so perhaps CustomersSalesLink - but your REL prefix does the same job. (I suggest you give thought to Prefix / Suffix before you start! I like things grouped alphabetically so we use NounVerb - so usp_CustomerGet, usp_CustomerSave, usp_CustomerDelete that way all my Customer stuff is grouped together, rather than all my Gets being grouped! Becomes more important when you have non-standard names as well like usp_CustomerDoThis, usp_CustomerDoThat ..."Use only 1 sproc, but have a extra parameter that tells the sproc what action to take"Not so good for the optimiser ... unless you doIF(@Action = 1) EXEC usp_CustomerGetbut then you haven't reduced the number of Sprocs!"I will quite often use a single upsert sproc rather than an insert and an update"We do that with our "xxxSave" Sprocs. They take a parameter to indicate CreateNew (must not exist), Update (Must exist) or "Don't care, create if does not exist""prepare for some controversy"Yeah, too much anal stuff in & around this. They are only opinions. Adopt the ones you think will work for you.However, I do have one piece of advice: Whatever you do, Be Consistent.Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-05 : 05:55:46
|
quote:
Originally posted by Kristen
However, I do have one piece of advice: Whatever you do, Be Consistent.
That is the golden rule. I had a boss once who kept hammering into me that there can be no flexibilty without structure. You can change\ tweak your conventions if you had them in from the start but lord help you if you decide to introduce them into a mature project that had none in the first place. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-05 : 06:38:06
|
| If we change our coding conventions [i.e. not just naming conventions ...] we go back and fix/change everything that is effected. So not a decision we take lightly!Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-07-05 : 11:08:42
|
| CustomersSalesCustomersGetCustomersInsertCustomersDeleteCustomersUpdatee4 d5 xd5 Nf6 |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2007-07-05 : 13:24:47
|
| Kristen,I don't even like doing the usp_ prefix. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-05 : 17:12:32
|
| "I don't even like doing the usp_ prefix"Well that doesn't matter, like I said its just opinions. Use CamelCase or whatever you fancy instead.Kristen |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-07-05 : 17:41:47
|
| Yes be consistant. :)I happen to be on the side of the fence that does not puralize the table names, but whatever workd for you. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-06 : 04:31:54
|
| You'll find tons on this on the web. Here's one we had recently at dbforums:http://www.dbforums.com/showthread.php?t=1618596 |
|
|
|
|
|
|
|
|
naming convention:rose.by.anyothr.name.would.sweet