Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004

    shared domains - make tables or bulk table?

    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:

    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

  2. #2
    Join Date
    Sep 2002

    Re: shared domains - make tables or bulk table?

    What's so "shitty" about little tables? A lot simpler than this over-complicated "shared domains" idea I'd say.

  3. #3
    Join Date
    Jan 2004

    Thumbs up Re: shared domains - make tables or bulk table?

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

    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.

    ATT# Value ID
    1 Household appliances P1
    1 Electronics P2

    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)
    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.

  4. #4
    Join Date
    Sep 2002

    Re: shared domains - make tables or bulk table?

    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:

  5. #5
    Join Date
    Jan 2004

    Red face

    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)
    Last edited by Machiel; 01-30-04 at 19:49.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts