Saturday, September 6, 2014

You Can't Put Snow In a Database Table -- How to Name And Think About Database Tables

I blogged previously about the considerations involved in choosing between singular and plural database table names.  In the course of getting input on the considerations involved, I came across a concession saying that a "news" table would always be plural.  But this post explains why you can't put snow, sunshine, love, mud, furniture, a survey, or news in a database table.

Of course, you can model any of those things in a database.  And any of them might make an ideal name for a database.  But not for a table.  This is because they are all what we call mass nounsA mass noun is a noun whose referents are not thought of as separate entities. Yes, there are concepts that measure, report, or describe a mass noun.  And you can model those concepts as discrete items in a table. But the mass noun itself has no separate entity that can be entered into a row of a table.  So a mass noun is NOT an appropriate name for a database table.

But what would be good names for tables about concepts that model those mass nouns?  For some of them, that may be an elusive question for humanities greatest thinkers, but here are some examples:

Snow

We can have a database about or partly about snow. In that database we can have tables that help model snow.
We may have a snow_measurement table with columns for measurement date, latitude, longitude, depth, technician, method, water equivalent, etc.
We may have a snow_storm table with columns for start_date, duration, depth, water_equivalent, drift_temperature, max_air_temperature, min_air_temperature, station_id
We may have a snow_word table with columns for word, ISO language code, and description

Sunshine

We can have a database about or partly about sunshine. In that database we can have tables that help model sunshine.
We may have a sunshine_intensity_measurement table similar to the snow measurement table.

News

We can have a database about or partly about news. In that database we can have tables that help model news.
We may have a story or article table.  We may have a reporter table.  We may have a deliverer table.  We may have a customer table.  All of this is about our business: news.

Furniture

We can have a database about or partly about furniture. In that database we can have tables that help model furniture.
We may have a (furniture) product table.  We may have a (furniture) inventory table.  We may have a (furniture) sale table.  We may have a (furniture) invoice or purchase_order table.  We may have a (furniture) shipment table.

Conclusion

Database tables are really a collection of database rows, and they should be named as though we were naming the thing that a row represents, whether that is a news article, a snow measurement, a snow storm, a piece of furniture, or a hug_event.  And lots of tables put together attempt to model something we keep together in the same database.

2 comments:

Anonymous said...

It's funny I never paid attention to whether tables I create are plural or singular untill I found your blog. I went over some databases I designed in the past and looks like it was 50-50

Thomas Gail Haws said...

It's sure interesting to think about.