[thelist] Database Naming Conventions

Hershel Robinson hershelr at netvision.net.il
Wed Mar 15 13:30:32 CST 2006

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?


Gallery Robinson Web Services

More information about the thelist mailing list