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:
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:
in table terms:
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:
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.:
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