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:

ATT# VALUE
17 Household appliances
17 Electronics
19 Magnetron
19 Housewife
19 Adsl modem

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
Unity 21
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

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
(ProductgroupId, productgroup)

Then you have a cross-table
(ArticleId, ProductgroupId)

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.

ATT# Name
1 Productgroup
2 ArticleId
etc.

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!!

(ArticleId, Productgroupmetanumber, description, etc...)

I store value 1 in productgroupmetanumber (it's a fixed field) and so I have access to the picklist.
Picklist:

ATT# Value ID
1 Household appliances P1
1 Electronics P2
17

All resulting cross-tables can be put into one cross-table without redundancy. For instance the bulk cross-table:

(ID stores the foreign key table which refers to the chosen value)
ID PICKID
A01 P1
A01 P5
CD1 P13

In this way I get rid of all small picklist tables and all cross-tables and save a huge amount of data! just by using metadata perspective


Now I have to find the way to let the user choose which fields should hold history and the same trick can be done with history by using metadata perspective.

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:

Table Entity( EntityID, EntityType )
Table Attribute( EntityID, AttributeID, AttributeName, DomainID )
Table Domain( DomainID, DomainName )
Table Value( DomainID, ValueID, ValueDesc )
Table AttributeValue( EntityID, AttributeID, ValueID )

So to record that there is an Employee called John hired on 01/01/2004 and paid $10000 you would enter something like:

insert into entity( 123, 'Employee' );
insert into AttributeValue( 123, 43, 'John' );
insert into AttributeValue( 123, 92, '01/01/2004' );
insert into AttributeValue( 123, 13, '10000' );

Lo and behold, you have a database with a handful of tables where you can store anything you like. The downside is - well to save repeating myself I'll refer you to this posting from earlier today:

http://www.dbforums.com/t980794.html

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)