I've got some tables which attributes have a shared domain.
For instance I have the field 'product group' in several tables.
The domain for product group is dynamic. New product groups can be added and product groups can be deleted.
I could see product group as an entity with its attribute but I have a lots of these small tables and would like to put all my 'picklists' in a lookup table. So factually I want it to be denormalized so I lose all this shitty little tables.
Every attribute can be considered an attribute number, let's say product group has att# 17 and product subgroup att# 19. Within a bulk table I could store it like this:
How can I easily achieve - using this philosophy - to get my 'shared domains' retrieve info from this bulk table? the problem is I can't store an att# per field and I can't link on the field name in a cross table.
A cross-table should look like this
Field name ATT#
Product Group 17
Sub product group 19
Short code 23
The originating table looks up the ATT# in the cross-table and uses it as a cross-through to the picklist. But I'm stuck with the problem. Help me
Originally posted by andrewst
What's so "shitty" about little tables? A lot simpler than this over-complicated "shared domains" idea I'd say.
Thanx for making me think again, I think I've found the resolution!!
Ok, let me explain I think my posting is too large.
You have table product groups
Then you have a cross-table
The same story like above for small attributes like Status, subproduct group etc.
I've once worked with a model with a centralized picklist (like saleslogix CRM has) but I can't remember the way they did it.
I also want to keep track of history and the idea of a a centralized picklist also makes a centralized history table possible.
In advanced data-modelling they have a way to do this and I've gone through InformIt.com (have a subscription to read the online books) but can't find it.
By looking at your attributes from metadata perspective you can give them a key number.
So you can store all your tiny tables into one bulk table. All small attributes can be put into one table without redundancy, their metakey is the key to access them. While typing this I see the resolution!!
You take the high road and I'll take the low road...
I didn't really follow all of that - like, what a "cross-table" is? But it sounds like the good old "funky data model" (Tom Kyte's name for it), where you have an "Entity" table, an "Attribute" table, a "Domain" table and a "Value" table and so on:
Ok Andrew, I fully understand your posting and it puts me to shame.
As being into reporting tools for some years (developer at the backend) I knew how hard it was to tackle these databases in flat reports and how terrible these kind of picklists are (u have to use parameters with the exact attribute values and so on). I really damned the developers which were doing these kinds of tricks and now eheh..well I understand why it's seductable (hey boss, look u have a fully flexible model).
And me as a crystal developer got all the blame, the boss said: why are these reports taking such a long time.
So I fully agree with you, later on I gonna regret It! But the cool things of these kind of models is users are flexible with their history and can even define their own entities.
Do you have an alternative to keep track of history without having exact copies of the tables? (normally you would have article and articlehistory, but i guess there are better ways)