[thelist] Database Naming Conventions

[Tab Alleman]

My company uses a similar naming convention.  At first I didn't like the "tbl..." convention, until it was explained to me that it was to distinguish tables from views.  This actually does come in handy when debugging code that contains adhoc SQL.  When you see "SELECT * FROM tblWhatever", you know the data is coming from a table and not a view.  

I'm personally a fan of the second convention, but I don't see why it should ever be anything but a personal preference.  I'd rather my column names be overly verbose than possibly confusing due to duplication in other tables, ambiguous abbreviations, etc.  

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of Hershel Robinson
> Sent: Wednesday, March 15, 2006 2:31 PM
> To: Thelist
> Subject: [thelist] Database Naming Conventions
> 
> 
> I am managing the build of version 2 of a certain webapp. The primary 
> developer of version 1 is not available to work in earnest on 
> version 2, 
> but he is entirely willing to assist, advise and consult. He 
> also wants 
> to provide the database schema which he feels will best fit 
> the needs of 
> version 2.
> 
> The coding will most likely be outsourced so long as a suitable 
> outsource team can be located.
> 
> There are two naming conventions which this ex-primary developer is 
> suggesting and I am unsure if they make sense. To preface, the system 
> will use CrystalCharts, a tool with which I am not yet 
> familiar, but one 
> which has views which can be (apparently) materialized or not 
> materialized.
> 
> His first convention is thus:
> 
> All objects are prefixed by a two-character object identifier 
> followed 
> by an underscore.
>      a) tb – table
>      b) vw – view
>      c) mv – materialized view
> 
> The second convention is that the primary index for each table is not 
> simply id, it is tablename_id. Foreign keys are foreigntablename_id.
> 
> Thus we have a table of publishers called tb_publishers. We 
> have a table 
> of publications called tb_publications. In the 
> tb_publications table we 
> have a primary key called publication_id and a foreign id called 
> publisher_id, which obviously links to the tb_publishers table.
> 
> I myself find both of these conventions a bit unusual. I am 
> uncertain if 
> the tb_ is necessary before a table name. In my experience, 
> one does not 
> generally confuse table names with field names. Perhaps I am wrong, 
> however. He feels it will be of assistance in debugging.
> 
> Also I believe standard practice (this is a PHP/MySQL project) is to 
> name the primary index id. This developer says that using 
> tablename_id 
> adds clarity and will help prevent the developers from using 
> shortcuts. 
> I am not certain to what 'shortcuts' he meant to refer, but I 
> figured I 
> would give the matter some thought before responding further.
> 
> Anyone have any thoughts about this?
> 
> Thanks,
> Hershel
> 
> --
> Gallery Robinson Web Services
> http://web.galleryrobinson.com/
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>