Re: Is there any recommended Naming Convention for Setting up DB Tables and Fields?
Date: 1996/05/12
Message-ID: <4n5gls$spc_at_lal.interserv.net>#1/1
> mqader_at_facstaff.wisc.edu (Mirwais Qader) writes:
> He wants to name all our tables as 2 or 3 characters.
> Ex. Customer = C or Cs or Cust
> Equipment = eq
> Media = med
> Phone_Log = fonelg
> Employee = emp
We name a table for the entity which it represents, in singular form. An entity is a type of
real-world person, place, thing or concept which encompasses multiple instances that can each be
represented by a single row with a unique primary key. Except for "Employee" I would say these entites
are not well chosen. "Equipment" and "Media" don't seem to represent anything. "Phone_log" is a
process-oriented name; a better name would be "Phone_call" or perhaps "Contact" to provide for means of
communication other than by phone. If everybody understands what entity a table represents,
relationships among tables will be more intuitive and denormalization (assignment of an attribute to the
wrong entity) will be more obvious.
Once you have a list of the entities, you may choose to abbreviate some. If you do, I recommend that you publish a list of standard abbreviations and always use the abbrevation consistently, never the spelled-out word. Relationships like
EMPLOYEE <----->> EMPL_DEPENDENT
are harder to find than
EMPLOYEE <----->> EMPLOYEE_DEPENDENT.
FONELOG is a particularly bad abbreviation of PHONE_LOG since it has no "P." It will not occur where expected in alphabetized lists. Users who are trying to get by without constantly referring to your data dictionary (you have one, right?) will consistently fail to guess what you named PHONE_LOG. Trust me, this is a bad idea!
Expanding on this logic, columns should be named for the attributes they represent, and if abbreviated should be standardized as above. Columns which appear in multiple tables need to have identical names, or the relationships among tables are harder to find. Some client software such as Borland's REPORTSMITH will take advantage of the identical names to suggest relationships for you during design of a join.
It is helpful to suffix a column name with a units designator (which should also be standardized).
PAYMENT_AMT
SENIORITY_YRS
CAPACITY_LBS
Good luck!
- Paul de Anguera, City of Seattle / HRIS