If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Tricky design

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-01-04, 08:06
whatkoist whatkoist is offline
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old 03-01-04, 08:35
andrewst andrewst is offline
Moderator.
 
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?
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old 03-01-04, 08:52
whatkoist whatkoist is offline
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old 03-01-04, 09:28
andrewst andrewst is offline
Moderator.
 
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.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #5 (permalink)  
Old 03-01-04, 09:33
whatkoist whatkoist is offline
Registered User
 
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?
Reply With Quote
  #6 (permalink)  
Old 03-01-04, 09:37
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Quote:
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...
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On