I was wondering what table naming conventions people use.  Not so much
interested in the column names as the table names themselves and how the
names describe the contents and relationships to other tables.

Eric,

<< I was wondering what table naming conventions people use.  Not so much
interested in the column names as the table names themselves and how the
names describe the contents and relationships to other tables. >>

A lot of books simply recommend using the noun that that table represents,
minus any plural form, such as "customer" for a customers table, "order" for
an orders table, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:2458D3AE-42D4-4B49-BC59-00C1620F59E0@news.elevatesoft.com...
> Eric,
>
> << I was wondering what table naming conventions people use.  Not so much
> interested in the column names as the table names themselves and how the
> names describe the contents and relationships to other tables. >>
>
> A lot of books simply recommend using the noun that that table represents,
> minus any plural form, such as "customer" for a customers table, "order"
for
> an orders table, etc.

I was looking for names of lookup tables, detail tables, cross refs, etc.
Tables that relate to a main table and how they're named to indicate the
relationships.  I've been doing this stuff a long time and sometimes I get
curious what other people do so that I don't get into a rut.

Hi Eric,

I do basically what Tim said i.e. non-plural noun .. related tables I
prepend the "owner" tables name e.g.

Customer
CustomerOrder
CustomerOrderItem

Lookup/details tables are named the same as other primary tables

--
Best regards

Steve

"Eric Schwarz" <eric@HATE-THAT-SPAMcomputer-strategies.com> wrote in message
news:F97F6298-0B07-4D09-96F0-563C06389F08@news.elevatesoft.com...
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:2458D3AE-42D4-4B49-BC59-00C1620F59E0@news.elevatesoft.com...
>> Eric,
>>
>> << I was wondering what table naming conventions people use.  Not so much
>> interested in the column names as the table names themselves and how the
>> names describe the contents and relationships to other tables. >>
>>
>> A lot of books simply recommend using the noun that that table
>> represents,
>> minus any plural form, such as "customer" for a customers table, "order"
> for
>> an orders table, etc.
>
> I was looking for names of lookup tables, detail tables, cross refs, etc.
> Tables that relate to a main table and how they're named to indicate the
> relationships.  I've been doing this stuff a long time and sometimes I get
> curious what other people do so that I don't get into a rut.
>
>
>

Let's say you have a Company table that has a 1-N relationship with a table
that contains codes for the possible classifications for each company, e.g.,
manufacturer, distributer, etc.  In addtiion, you have a lookup code table
that contains all the classifications and their descriptions.  Any
interesting naming conventions for that scenario?  Depending on the naming
conventions used, the table names in combination with field names can be
interesting/confusing/conflicting.

"Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> wrote in message
news:62BA8E52-554D-4ED4-B28B-A2C776E3464D@news.elevatesoft.com...
> Hi Eric,
>
> I do basically what Tim said i.e. non-plural noun .. related tables I
> prepend the "owner" tables name e.g.
>
> Customer
> CustomerOrder
> CustomerOrderItem
>
> Lookup/details tables are named the same as other primary tables
>
> --
> Best regards
>
> Steve
>
> "Eric Schwarz" <eric@HATE-THAT-SPAMcomputer-strategies.com> wrote in
message
> news:F97F6298-0B07-4D09-96F0-563C06389F08@news.elevatesoft.com...
> >
> > "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
message
> > news:2458D3AE-42D4-4B49-BC59-00C1620F59E0@news.elevatesoft.com...
> >> Eric,
> >>
> >> << I was wondering what table naming conventions people use.  Not so
much
> >> interested in the column names as the table names themselves and how
the
> >> names describe the contents and relationships to other tables. >>
> >>
> >> A lot of books simply recommend using the noun that that table
> >> represents,
> >> minus any plural form, such as "customer" for a customers table,
"order"
> > for
> >> an orders table, etc.
> >
> > I was looking for names of lookup tables, detail tables, cross refs,
etc.
> > Tables that relate to a main table and how they're named to indicate the
> > relationships.  I've been doing this stuff a long time and sometimes I
get
> > curious what other people do so that I don't get into a rut.
> >
> >
> >
>
>

Hi Eric,

If I understand your schema correctly, I would name as follows:

Company
CompanyClassification
Classification

--
Best regards

Steve

"Eric Schwarz" <eric@HATE-THAT-SPAMcomputer-strategies.com> wrote in message
news:16E1C785-E050-4C9A-9271-6AE7E27EBBAA@news.elevatesoft.com...
> Let's say you have a Company table that has a 1-N relationship with a
> table
> that contains codes for the possible classifications for each company,
> e.g.,
> manufacturer, distributer, etc.  In addtiion, you have a lookup code table
> that contains all the classifications and their descriptions.  Any
> interesting naming conventions for that scenario?  Depending on the naming
> conventions used, the table names in combination with field names can be
> interesting/confusing/conflicting.
>
> "Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> wrote in message
> news:62BA8E52-554D-4ED4-B28B-A2C776E3464D@news.elevatesoft.com...
>> Hi Eric,
>>
>> I do basically what Tim said i.e. non-plural noun .. related tables I
>> prepend the "owner" tables name e.g.
>>
>> Customer
>> CustomerOrder
>> CustomerOrderItem
>>
>> Lookup/details tables are named the same as other primary tables
>>
>> --
>> Best regards
>>
>> Steve
>>
>> "Eric Schwarz" <eric@HATE-THAT-SPAMcomputer-strategies.com> wrote in
> message
>> news:F97F6298-0B07-4D09-96F0-563C06389F08@news.elevatesoft.com...
>> >
>> > "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
> message
>> > news:2458D3AE-42D4-4B49-BC59-00C1620F59E0@news.elevatesoft.com...
>> >> Eric,
>> >>
>> >> << I was wondering what table naming conventions people use.  Not so
> much
>> >> interested in the column names as the table names themselves and how
> the
>> >> names describe the contents and relationships to other tables. >>
>> >>
>> >> A lot of books simply recommend using the noun that that table
>> >> represents,
>> >> minus any plural form, such as "customer" for a customers table,
> "order"
>> > for
>> >> an orders table, etc.
>> >
>> > I was looking for names of lookup tables, detail tables, cross refs,
> etc.
>> > Tables that relate to a main table and how they're named to indicate
>> > the
>> > relationships.  I've been doing this stuff a long time and sometimes I
> get
>> > curious what other people do so that I don't get into a rut.
>> >
>> >
>> >
>>
>>
>
>

>If I understand your schema correctly, I would name as follows:
>
>Company
>CompanyClassification
>Classification

I use a very similar naming convention to Steve (great Steve's think
alike but the Oracle databases here use upper case and have LK_
prepended to lookup tables, eg. LK_CLASSIFICATIONS.  Personally I
prefer Steve's and Tim's naming conventions and have been using this
for years.

Regards,

SteveG

Steve Gill <steve@archaeologist.com> wrote:

I have written entirely too many apps. Fortunately over the years, I've learned a lot about code reuse. That includes tables with all sorts
of stuff, ranging from types of credit cards including pre-paid cards (7-11 gift card)  (HUNDREDS) to different brands of overnight delivery.
Maybe not OOP, but my many vertical market apps have lots of tables for lookups.

What I settled on is a naming scheme that works with both different applications and tables within them. That is one day I'm writing a CRM
for me and then next week doing an app for radio stations. Even though they are really different they still share (even if in an utility like a
phone number list) a lot of tables.

So at least I have to know when I look at a table PRECISELY what program it is from and what it does. One of the first things I do is
create a table listing the tables, and often the reverse engineered DBISAM SQL code to create an empty (or populated table) is stored as
a memo.

Not sure how this fits in with "best IT practices" because I have only seen table naming discussed in //// shudder //// SAP. Probably had I
attended computer school rather than law school (guffaws) I would have learned it formally. Nevertheless I have lots of text books that
I've read and I don't remember reading about this either.

Scheme

First is the program

for example, let's pick a radio station application; let's call it DJ

DJ_   will start the tables that are application specifc , let's say requests for a radio station

DJ_requests

then, after some field experience I find out (or knew) that requests come in letters, phone calls, messages taped to rocks heaved through
windows, the station web site and several emails (each DJ or the station's generic email) and the station wants to track how requests are
made.

The lookup is then:

DJ_requests_type_lu

Therefore I can tell that this table came from the DJ application, and is the requests table field "type" lookup.

I also have master/detail nomenclature:

DJ_requests_music_group
DJ_requests_music_group_d

Some tables are of similar types

PasswordFailureErrors
BackupJobLog

but store data just for one program therefore we get:

DJ_PasswordFailureErrors

As for tables designed for reuse (read only), I use nouns without any prefix:

states
areaCodes
creditCards
Countries

I use the DBISAM version number if I improve my list of states or area codes, etc. That way I know if I have the latest list of countries,
zip codes or whatever.

Therefore, I know the program, function and type (table, master, detail, lookup list, log, errors)

It works for me

Herb

Hi Steve,

> (great Steve's think alike

Absolutely, BTW have I congratulated you on a great name? <bg>
--
Best regards

Steve

"Steve Gill" <steve@archaeologist.com> wrote in message
news:hu4ma2tj22efr4qiugee1fmgthce6vajhn@4ax.com...
>
>>If I understand your schema correctly, I would name as follows:
>>
>>Company
>>CompanyClassification
>>Classification
>
> I use a very similar naming convention to Steve (great Steve's think
> alike but the Oracle databases here use upper case and have LK_
> prepended to lookup tables, eg. LK_CLASSIFICATIONS.  Personally I
> prefer Steve's and Tim's naming conventions and have been using this
> for years.
>
> Regards,
>
> SteveG

Interesting - I'll review that for consideration here. I'll stick in the
scheme that has worked well for me, which is based on the idea that I want
to stop silly mistakes creeping in. The table names are all paired words,
so they will be things like TaskList and FtpFiles. With that, each
field then always uses the two letter (okay, sometimes three if need be)
table name as a prefix. We've renamed tables to make sure there are no
clashes. So we'd have tlTaskID and ffFileID and ffFileName, ffUploadName
etc.

Where this helps is in queries. I've seen others do this, and I used to do
it before, where you'd have a "WHERE (ID = REFID)" and of course it would
turn out that the ID was using the wrong database's field and thus
comparing apples and oranges. Sure, you can put the table reference as a
prefix, but it works very well to have a prefix that ensures you can't get
it wrong. Most mistakes seem to happen in the copy and paste from other
similar queries, and this makes sure that the fields are relevant. It's
saved my bacon a few times, and it also saves all those table prefixes and
aliases which can clutter a query.

/Matthew Jones/