RE: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names

[Greg Low]

Hi Michael,

Where would you use each?

Regards,
Greg

Dr Greg Low
Readify | Senior Consultant
Suite 206 Nolan Tower | 29 Rakaia Way | Docklands | VIC 3008 | Australia
M: +61 419 201 410 | E: mailto:greg.low@xxxxxxxxxxx | W: www.readify.net


-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Michael Silk
Sent: Monday, 9 July 2007 4:15 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [SQLDownUnder] [SQLStyle] #2: Prefixes on Table/View names

i don't see schemas and prefixes being mutually exclusive.




On 7/9/07, Greg Low <greg.low@xxxxxxxxxxx> wrote:
>
>
>
>
> #2: Avoid using prefixes on table/view names.
>
>
>
> Some people like prefixes like vw or v on view names. Some even like tbl on
> table names. There are several problems with this.
>
>
>
> First up, it's common in production systems for views to replace tables and
> (occasionally) vice-versa. From a user's perspective, the two should be
> identical. As a related example, Microsoft have learned this lesson the hard
> way even in SQL Server with their stored procedure naming. SQL Server system
> stored procedures are prefixed with sp_ and extended stored procedures with
> xp_. However, if you now look at the list of each, you'll find system stored
> procedures with xp_ prefixes and extended stored procedures with sp_. That's
> worse than if they'd never have any scheme because now it's very confusing.
>
>
>
> The second common version of this is where tables or views are prefixed with
> something that relates to where they are used in the system. The reason
> typically given for this is to avoid having to look through massive lists of
> tables in management tools, etc. However, SQL Server 2005 has introduced
> schemas and invariably, these are a better replacement for a prefix naming
> convention. Schemas have the added advantage of also being a security
> boundary and rather than granting SELECT permission on a series of tables
> named:
>
>
>
> HR_Table1
>
> HR_Table2
>
> Etc
>
>
>
> You'd often be far better off granting SELECT permission on a HumanResources
> schema that contained tables named: Table1, Table2, etc.
>
>
>
> In the example Michael gave the other day of using tmpCustomers as a staging
> table for Customers and tmpOrders as a staging table for Orders, even in
> this case, I think it would be preferable to have a Staging schema that
> contained these tables and that they'd become:
>
>
>
> Staging.Customers
>
> Staging.Orders
>
>
>
> And the "real" tables would be something like:
>
>
>
> Sales.Customers
>
> Sales.Orders
>
>
>
> It's highly likely that all the staging tables would also need the same
> permission settings and again, these could likely be best done at the schema
> level.
>
>
>
> Regards,
>
> Greg
>
>
>
> Dr Greg Low
>  Readify | Senior Consultant
>
> Suite 206 Nolan Tower | 29 Rakaia Way | Docklands | VIC 3008 | Australia
>
> M: +61 419 201 410 | E: mailto:greg.low@xxxxxxxxxxx | W: www.readify.net
>
>   to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net


--
mike
http://lets.coozi.com.au/


to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net




to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net