The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database terminology

Assuming I have a database that manages information for shops. The shops are stored in a table named "shops", and each of them has many unique categories in a table named "shop_categories".

I also have a root categories table in my system named "categories". I need to link every shop to many root categories using a reference table - normally, I would have named it "shop_categories", but this name is already in use.

How should I call it?

It can get very confusing once you start having many tables - is there any mysterious standard for naming tables?
Sagi B Send private email
Thursday, January 20, 2005
There is no standards just do what makes sense and avoids confusion. Change Shop_Categories to Shop_Types
Thursday, January 20, 2005
Don't know if it'll help but I usually only use an underscore in a table name if it's a reference table.  So you would have:


Also note that it's best practice to not name your tables in the plural.

As with all items of this nature, there's no strict "rules" but the ones you come up with yourself.
Thursday, January 20, 2005
>>Also note that it's best practice to not name your tables in the plural.

I've never understood this. Is there a logical reason for it, or is it just an industry practice?
Thursday, January 20, 2005
I'd like to hear any other "best practices" from smallbiz and others with more experience than me.  Even if they're not official best practices, what do *you* do?

I'm fairly new to database design, and as I get involved with more complicated projects, it would be nice to hear some bits of wisdom.
EAW Send private email
Thursday, January 20, 2005
"Also note that it's best practice to not name your tables in the plural."

That's funny because I've heard the opposite; a table represents a set of entities and is therefore plural.  Any good reason for it to be plural or not plural?
Almost Anonymous Send private email
Thursday, January 20, 2005
"I've never understood this. Is there a logical reason for it, or is it just an industry practice?"

Well...I don't know the exact reason why people advocate this but my guess is:

1) Singular names more closely mirror ER diagramming.
2) It's generally accepted as a given that there'll be more than one row in a table so naming with plurals is unnecessary.

Again, whatever works for you is generally the rule of the day.  I'm sure if I had my mentors tell me to name everything in plurals I'd be doing that instead. :-)

One thing I do that definately is not advocated is prefix my column names with the table name.  If I have a table Shop then I'll have columns ShopID, ShopName, ShopDescription, ShopPicture, etc.  I got in the habit of doing this so I wouldn't have to alias columns in multijoin queries where tables had columns of the same name (like 'ID').  It generally drives my co-workers buggy cause they have to type so much when creating queries but, despite the extra typing, I like my style cause I'm in the camp of "type more, think less" when it comes to coding.  Burns my ass when people name their variables "st_xtr_fl" and such.
Thursday, January 20, 2005
I've seen a fair bit of shop_category_xref in that situation.
Art Send private email
Friday, January 21, 2005
I'm confused by your design here. What does the cross-reference table of root categories to shops give you that you don't already have in ShopCategories? In other words, you already know what categories are associated with every shop, don't you?

But if root categories are different than shop categories you have a much bigger naming problem.
Tom H
Friday, January 21, 2005
shop_categories contains the names (and other info) of shop level categories. Each shop has its own categories set, which are not related to the root categories.

It is an ecommerce management system, where the database contains data of many different web shops. The root categories are used in a central portal, mainly used to find a shop.

Perhaps I should have named the root table (root/portal)_categories.
Sagi B Send private email
Friday, January 21, 2005
There's certainly no technical reason for using the singular or plural form for a table name.  It mostly depends on your point of view, do you see the data in tuples or its entirety?

If tuples then it may make sense to use the singular, but if you think Orders.New() is more akin to adding a new instance to a collection then use that.

Whichever is chosen do it consistently.
Simon Lucy Send private email
Friday, January 21, 2005
There is one school of thought that says you should not name your tables in the plural, because of COURSE every table has multiple records, but itself it is a single table, thus name it singular.

There is another school of thought (more recent, I believe) that says it holds a group of records, so name it plural.

I don't care for the first argument myself.  I find it much clearer to think that my table's purpose in life is to hold a collection of things (generic use of "collection" here, OO people PLEASE don't go off on this).  Thus, I find it clearer to name my tables in the Plural, and a single record in the Singular.

And I do like the "_XREF" suggestion.  It alpha-sorts the name such that the _XREF table stays near at least one of the tables it is cross-referencing.
Friday, January 21, 2005
I am not sure if this is the reason, but using plurals did come back to bite me in the butt while referencing a table name in a procedure.  I had a case where I would send in the table name as a parameter and then use the table name to reference fields within the table. 
For example, I would have a table named "tblWidgets" that I would take in as a parameter.  I would then be able to reference other fields within the table (i.e. WidgetID, WidgetName, WidgetDesc, etc) by slicing off the first 3 and last letter, and then concatenating it with whatever field I wanted to reference (i.e. ID, Name, Desc).
This came back to bite when I had a table named "tblActivities" with field names "ActivityID", "ActivityName", and so on.  It had made sense to me to make the table name plural, but not the field names.  It wasn't a big deal to account for my bad naming convention, but it just created the need for unnecessary code (especially when I had two other tables in the same situation).

Is this the reason for not using plurals?  I don't know.  Will I use plurals in future databases?  No.
Jay Send private email
Monday, January 24, 2005
Talking about table names, what are your comments on the table names used by SAP internally? 

I always thought they used a random character generator to come up with those names!
Arun Philip Send private email
Tuesday, January 25, 2005
Does everyone prefix their tables with "tbl"?  I've always considered that redundant and I never do it.  What about prefixing all the fields with the table name (as in the above posting: WidgetID, WidgetName, etc).  I also don't do that.  I do WidgetID but after that it's all Name, Desc, etc.

How common is that one way or the other?
Almost Anonymous Send private email
Tuesday, January 25, 2005
"Also note that it's best practice to not name your tables in the plural."

The Shop (entity) has 1 category.
The Shop (entity) has 1 type.

A table should represent an entity. Why plural?
Tuesday, January 25, 2005
"A table should represent an entity. Why plural?"

Table is set of entities -- it doesn't represent an entity.  A row in a table is an entity.  That's why plural.
Almost Anonymous Send private email
Tuesday, January 25, 2005
A row in a table is called tuple. An entity is defined by a predicate, which is in fact a way to define a set.

X = { x | predicate }
Wednesday, January 26, 2005
Here's a great article on naming classes. I think the ideas presented translate pretty well to naming database tables.
Chad Baker Send private email
Thursday, January 27, 2005
First off, the first rule of naming should be obvious:

Use a convention.

It can be your own, but use a convention.

On pluraled names: Jay's example illustrates one great reason to not use plurals. Another is sorting. If I have tblContract and tblContractRenewals and tblContractSigners, they sort such that tblContract comes first, and the related tables come after it.

JeremyNYC Send private email
Friday, January 28, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz