Results 1 to 6 of 6

Thread: Tricky design

  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Tricky design

    So... dear friends.
    I have three tables. Products, Sales, and SaleDetails. I have a very big problem. Each Product has different SaleDetails fields. I want to find a design concept based on that, without having to make a new SaleDetail table for each Product. Putting all the detail fields of all the products in a table is not an option too.
    Products(ProductID(PK), ProductName,...)
    Sales(SaleID(PK),ProductID(FK),Date,SaleDetailID(? ??)).
    If it isn't too complicated please answer soon. Thanx!
    I feel dizzy, too much coffein in my blood, and i'm still getting these error messages. Well doctor?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Tricky design

    Can you give some examples of the sort of details different products would have? And how many different products?

  3. #3
    Join Date
    Mar 2004
    Posts
    13

    Details explanation

    Thanx andrewst.
    For example.... As products I have a bank account and an insurance. As you see the only common part is the customer name. don't want to have a restriction to the number of the product entries.
    I feel dizzy, too much coffein in my blood, and i'm still getting these error messages. Well doctor?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Details explanation

    Right. Presumably there are various Insurance products, and maybe various Bank Account products? So perhaps there is a Product_Type associated with each Product.

    Then you could subtype the Sales_Detail table per Product_type:

    Sales_Detail_Bank_Account( Sales_Detail_ID, <bank account details> );
    Sales_Detail_Insurance( Sales_Detail_ID, <insurance details> );

    I know that you may have many different Insurance Product_Types, but they would presumably have quite a lot in common, e.g. Sum Insured, Start Date, End Date, ... You could perhaps have a few additional, generic columns for unantipated attributes in future products(num_value_1, etc. - not elegant, I agree!)

    From what you said originally, I don't expect you like this approach. You appear to be more inclined towards a super-generic design like the often used (and detested by me!) "Entity-Attribute-Value" (EAV) design.
    This allows users to specify the attributes of each Product without changing any tables, but requires many rows of data to hold one logical record, e.g.:

    insert into sales_detail( sales_detail_id, att_name, att_value )
    values (123, 'Account Number', '1203212');
    values (123, 'Start Date', '01-JAN-2004');
    values (123, 'Opening Balance', '59.99');

    That way madness lies, IMHO! It also obviates use of database constraints (foreign keys, chekc constraints) to ensure data integrity.

  5. #5
    Join Date
    Mar 2004
    Posts
    13

    Thumbs up

    It's a very interesting approach andrewst. Thank you very much!
    I feel dizzy, too much coffein in my blood, and i'm still getting these error messages. Well doctor?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by whatkoist
    It's a very interesting approach andrewst. Thank you very much!
    What, the EAV approach? Oh no, what have I done - I was trying to put you off it...

Posting Permissions

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