Primary Key

Please help:

I am creating a table called Bonus:
ProductHeading1
ProductHeading2 (could be null)
ProductHeading3(could be null)
Bonus
Datefrom
DateTo

... what would be the primary key?! I know it would be DateTo and sumfing..... Since Heading2 and Heading3 could be null, they cannot be PK... and heading1 cannot be a PK because the following three DIFFERENT options could have the same heading1
Option 1) heading1 = "X" heading2 = Null heading3 = Null
Option 2) heading1 = "X" heading2 = "Y" heading3 = Null
Option 3) heading1 = "X" heading2 = "Y" heading3 = "Z"

... but I need a PK to make sure a bonus is not entered twice... I considered added an Id, but them how do I assign a id?! what would i make the id equal to?

Thanks...... but I need a PK to make sure a bonus is not entered twice... not entered twice for what?

if you pay a $50 bonus, does this mean you will never pay another bonus for $50? of course not!

so you have to ask yourself how do you identify the separate entities that are getting the bonus

what are these things, anyway? products? you want to pay a bonus on a product?

don't the product headings relate to a product table or something?

can the same three product headings occur again on different dates?

you need to give some more thought to exactly what this table represents|||Soz sounds like i aint explained the table properly!!
headings are product groups...
eg.

Heading1 - Heading2 - Heading3- ProductID
Clothing - T-shirt - Lady - 705921

Bonus (or Commission) is likely to be a percentage value, the sales team get for the products that fall into that heading...
eg Clothing might have an overall value of 20%
while Clothing - T-shirt - Lady might have a specfic value of 10% for 10/10/04 to 10/10/06 and then Clothing - T-shirt - Lady has value of 30% 11/10/06 - 10/10/07...|||if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself

the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels

you should really be putting the bonus on the product group in the product group table

and if you don't have a product group table, you should|||Sounds like time for a new table:

ProductID Bonus DateFrom DateTo


Unless you plan on offering different bonus amounts for the same product id in overlapping data ranges.

Or do you change the ProductID and have the same product under 2 (or more) ProductID numbers? (I once worked backend for a MAJOR big-box retailer ... that's how their folks handled discounted items ... same thing with different product id ... didn't agree with it, but was in no position to voice my concerns!)|||[QUOTE=tomh53]Sounds like time for a new table:

ProductID Bonus DateFrom DateTo


QUOTE]

that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30...

a product can onli be in 1 heading group, no overlapping...|||if your product groups are hierarchical (as they appear to be), then the bonus should be on the product group itself

the problem is that you have "flattened" the product hierarchy, and sometimes the hierarchy doesn't go down three levels

you should really be putting the bonus on the product group in the product group table

and if you don't have a product group table, you should

yup, its hierarchical...but what would the product group table have:

GroupID , heading1, heading2, heading3, bonus

BUT what do I do when the bonus is on

heading1, heading2... and not the whole Group?!|||create table productgroups
( id integer not null primary key
, productgroup varchar(37) not null
, parent_id integer null
)

a typical hierarchical structure, known as the adjacency model

see Categories and Subcategories (http://sqllessons.com/categories.html) for examples of querying this structure

link each product to whichever product group it belongs to

product groups at the top of their hierarchy have parent_id null|||create table productgroups
( id integer not null primary key
, productgroup varchar(37) not null
, parent_id integer null
)

a typical hierarchical structure, known as the adjacency model

see Categories and Subcategories (http://sqllessons.com/categories.html) for examples of querying this structure

link each product to whichever product group it belongs to

product groups at the top of their hierarchy have parent_id null

THANKS...looks promising (yet hard... think there is a lot of data so be a while 2 populate the table... currently they are just normal columns in my product table!) I'll give it a try and let u no how it goes...

Thanks agian :)|||sounds like it's time to normalize the model|||can i not write a insert query something lik this?!:

INSERT INTO productheadings VALUES
(SELECT distinct heading1 as name, NULL as parent_id from product)

basically I want to insert what is in my subquery in2 ma new table?!|||sounds like it's time to normalize the model

Someone else created the database...lucki me... i remba how much i LUVED normalisation at uni :S

Thanks :)|||Stucky,

Please read the sticky (is he a relation of yours) at the top of this board, and post your question in the terms that it asks for.

Probably will get a solution in, oh say, 5 minutes|||can i not write a insert query something lik this?!:

INSERT INTO productheadings VALUES
(SELECT distinct heading1 as name, NULL as parent_id from product)

basically I want to insert what is in my subquery in2 ma new table?!yes, you can write a query likje that

not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two

but you are on the right track

however, something is of great concern -- you have 3 headings in the product table?

this database is going to require a lot of redesign work...|||Stucky,

(is he a relation of yours)

nope :) just good minds fink alike :p

thanks again :)|||not exactly like that, though -- because the productgroups table has 3 collumns but you are supplying only two

this database is going to require a lot of redesign work...

yeh onli supplied 2 cos i made the id an identity so gets assigned by system!!

yeh it does seem lik it will be a long job... I have created the table and filled it wiv sum records... will create the front end (VB.Net) before filling the rest... just in chance the project spec gets changed!! :p

Thanks for all the quick responses :) :angel:|||that wont wrk cos it wont always go down to the product... the bonus maybe on Clothing overall?! eg All products in Clothing - T-shirts may have bonus 30...

a product can onli be in 1 heading group, no overlapping...

Just a thought ... will the bonus be cumulative or exclusive?

For example ... say shoes has a 20% bonus and baseball spikes have a 15% bonus. Will I get 35%, 20%, or 15%?|||Just a thought ... will the bonus be cumulative or exclusive?

For example ... say shoes has a 20% bonus and baseball spikes have a 15% bonus. Will I get 35%, 20%, or 15%?

exclusive i fink?!|||Hey ... im bak LOLS!!!

well now i have created the application which saves the headingID and bonus.... (Quick refresh: 1 product has three heading levels - the new app assigns a bonus to a heading at ANY level)

I have to now write an update query that will calculate the Bonus for each transaction on the products.... where it selects the Bonus for the heading (and makes sure the transaction date is between bonus dateTo and bonus dateFrom)... this is all fine!

The problem is the update query should be:

If heading 1 has an assigned bonus, the transaction should be calcuted using it
else if Heading2 has an assigned bonus, use this
else if heading3 has an assigned bonus, use this
else bonus is Null

how would I write this case select so that it stops as soon as it finds an assigned bonus???

THANKS :)|||hey, ive got a similar problem. actually, if i start explaining the exact situation, ppl will get confused.

but anyway, i only want to say that this situation is absolutely possible.

the problem is if we make them as a composite primary key, all attribs which are part of the primary key have not null constraints.

is there a way to create composite keys such that either of the attributes must have not null constraint or are the databases built in such a fashion to support only the former?

i think the only other way out would be to provide some default values to prevent the violation of not null!!! this method is very local(i mean non-standard) i guess.|||i still think the hierarchy is the better design

no problem with nulls then

see post #8 in this thread|||Hi

I have used the hierarchy table to create a table which has the headingID and assigned bonus(productheadings)... what I'm stuck wiv is, how do I use this table to calculate the bonus for each transaction (Calculted bonus = sales*assignedbonus)...

Each transaction is with a product which always has 3 headings...
what i need to do is
1) check if heading1 has an assigned bonus (within transaction date)
2) If not, check heading2
3) if no bonus for heading2, check for heading3

If a bonus is assigned at any of the 3 stages, the query should calculate bonus and stop (or else bonus column in link table is null)... I dont no how to write this part of the query...
If I was only looking at 1 heading I would write something like:
(SALES TABLE - where I would store TranscationID and calculated bonus
SALES_STOCK - product table
productheadings - heading table
SALES_STOCK_BONUS - Link table... where I would store headingID and calculated bonus...

SELECT SALES.Product, Bonus
FROM SALES
INNER JOIN SALES_STOCK ON
(SALES.Product = SALES_STOCK.Product
AND SALES.Whse = SALES_STOCK.Whse)
inner jOIN productheadings On
(SALES_STOCK.heading3 = productheadings.name and productheadings.headinglevel = 3)
inner join SALES_STOCK_BONUS on
SALES_STOCK_BONUS.id = productheadings.id
Group by SALES.Product, Bonus, SALES.[Date], SALES_STOCK_BONUS.DateFrom, SALES_STOCK_BONUS.DateTo
having SALES.[Date] >= SALES_STOCK_BONUS.DateFrom and SALES.[Date] <= SALES_STOCK_BONUS.DateTo

how would I change this query so that it is in a if loop.... please help!!!|||dear mr/ms stuck1234

please can you show your exact table layout

in one sentence you say you have created the hierarchy table, but in the next sentence you say "Each transaction is with a product which always has 3 headings"

what is going on? i fail to understand|||productheading hierarchy table has
headingID, dateto, datefrom, bonus, headinglevel (ie 1, 2,3)

SALES_STOCK
PK - ProductID, WHse
- here there are 3 headings associated with each product

SALES
PK - transaction table which should contain a column CalculatedBonus = (productheadings.Bonus * Sale /100)

I can see that the query I wrote is all wrong, it has an extra table in it...should a bit more like:

SELECT SALES.Product, productheadings.Bonus
FROM SALES
INNER JOIN SALES_STOCK ON
(SALES.Product = SALES_STOCK.Product
AND SALES.Whse = SALES_STOCK.Whse)
inner jOIN productheadings On
(SALES_STOCK.heading3 = productheadings.name and productheadings.headinglevel = 3) -- This bit is wrong.... how do get it 2 do the if statement I explained above
Group by SALES.Product, productheadings.Bonus, SALES.[Date], productheadings.DateFrom, productheadings.DateTo
having SALES.[Date] >=productheadings.DateFrom and SALES.[Date] <= productheadings.DateTo|||Would this work:

DECLARE @.Date INT
SET @.Date = (Select Date from SALES)
SET @.ID1 = (Select ID1 from SALES)
SET @.ID2 = (Select Date from SALES)
SET @.ID3 = (Select Date from SALES)

If (select Bonus from productheadings where ID = @.ID1
and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
BREAK
ELSE
If (select Bonus from productheadings where ID = @.ID2 and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
ELSE
If (select Bonus from productheadings where ID = @.ID3 and @.Date BETWEEN productheadings.StartDate and productheadings.EndDate) IS NOT NULL
(SELECT ...write query)
BREAK
END

I will check 2moro if it wrks... but I got a feeling the if statement is right...but declaring the variables at the top is wrong... but how else would i get that data for each line...|||you did not understand the hierarchy table at all

i am sorry, but i cannot help you any further

good luck|||The structure I sed yesda was wrong soz...

this is the current state of my tables:
SALES - transaction table
SALES_STOCK - stock table
productheadings - hierarchy table storing details on headings (id, name,parent_id, headinglevel)
SALES_STOCK_BONUS - stores headingid, DateTo, Datefrom, Bonus

Is the structure of my tables wrong???
How do I write a query to go through the productheading table and return the bonus,if there is one for each transaction...

I have seemed to have got majorly confused on this last night.... :(|||The structure I sed yesda was wrong soz...

this is the current state of my tables:
SALES - transaction table
SALES_STOCK - stock table
productheadings - hierarchy table storing details on headings (id, name,parent_id, headinglevel)
SALES_STOCK_BONUS - stores headingid, DateTo, Datefrom, Bonus

Is the structure of my tables wrong???
How do I write a query to go through the productheading table and return the bonus,if there is one for each transaction...

I have seemed to have got majorly confused on this last night.... :(|||I just wana know if my thinkin of this is right?!

SALES_STOCK now has a foreign key called headingID which is linked to productheadings.id... headingID will be the ID of heading1... so now i need to write a query which looks for the Bonus (in SALES_STOCK_BONUS) for headingID, which is the NODE in the hierarchy table, if NULL then look at node child1, if NULL look for Bonus in node child2...

Is this correct line of thought??