Canonically speaking

A co-worker and I recently got into a discussion regarding table naming – specifically prefixes and how to group them.

A common approach you see, is to group the tables around a system using a prefix for it’s name:

_newsevents_news
_newsevents_categories
_newsevents_acl

The hardest thing about keeping everything ‘integrated’ is to get other developer’s off of the concept that they need to build their own access control system, or specialized category organization – a category system is a category system, it’s more so a metadata heirarchy to organize data.

I’ve always adopted a canonical approach, similar to how dns server operate, start with what would be your end:

com.mydomain
com.mydomain2
org.anotherdomain

in table terms:

_acl_news
_acl_content_management
_categories_files
_categories_news

The reason I do this is to keep myself in check with how consistent my designing is –
the schema for ‘newsevents_categories’ and ‘file_categories’ in a conventionally grouped table set may vary widely – the idea is to wean off of these drastic changes and group them by their TRUE function, not just the ‘sub-system’ they compliment – and have their schemas as similar as possible. This ensures better object design and abstraction possibilities as well as truly faster development. You can trust yourself with this naming convention just as much as you can trust yourself with the conventional naming, it just takes a different perspective to have it make sense.

The real issue here is how we name our datastore tables for our various formats – if you have news posts, or faq items you’re likely to see tables like:

news_items
faq_questions

Ideally, these generic datastore tables would follow a naming convention revolving around what they truely are, the ‘data’ that drives the system (not to be confused with various metadata tables like category), e.g.:

_data_news
_data_faq

The idea here is to keep the schemas the same – everything has metadata and other supplemental information that makes it ‘different’, that’s what code is for, bringing those tables together to make them into specific objects.

There’s always the unknown – a good example of a wrench thrown into the mix is if you have file information stored in a table – possibly containing image dimensions, file mime types or category relationships – The thing that makes table designs so inconsistent is typically the metadata that revolves around the specifics of what it’s relevant to. This inconsistent, or ‘unique’ information should be stored boldly in a metadata table.

This post is a little long so I’m going to post the “bring it together” stuff in another post

Leave a Reply

Your email address will not be published. Required fields are marked *