Hello.
I'm building my first database ever (after reading a book or two) and I've come across a challenge. I'm having a hard time coming up with the solution, so I'm soliciting your help! The funny thing is that I think the answer must be easy, but because I've been looking at it for so long I can't seem to get out that 'loop' if you know what I mean!
ok here goes. I'm building a database of products for a small company. The challenging part is that some products are priced each, some have ranges (1-9 is $10.00, 10 and up is $8.00), and some have price breaks (50=$1.79 each, 100=$1.65 each, and so on). What makes it even more challenging are that many of the products have the same pricing structure. For example they sell 60 different banners that all carry the same price break structure.
So essentially it is a many to many relationship.
I originally set up a table PRICE, and it's fields were PriceID (prim key), PriceGroup, Quantity, Price. PriceGroup would be a number that related a group of prices together... for instance...
1_1_50_$1.79
2_1_100_$1.65
3_1_300_$1.50
4_1_500_$1.38
5_1_1000_$1.20
6_2_each_$19.95
etc...
Then in table Products I would relate each product to a PriceGroup.
But I've just learned that I can't relate those two tables b/c PriceGroup isn't a unique identifier. Hmmm...
The only other way I can think of doing it is with a linking table, and each product would be related to the PriceID. But that means if a product has 5 price breaks, it would be in the table 5 times. And if there are 60 products with the same price break structure, well... do the math. And I'd have to update each product with the same pricing structure seperately instead of just updating one price structure.
Any suggestions? Remember, I'm new at this so be nice!
Nicole
I'm building my first database ever (after reading a book or two) and I've come across a challenge. I'm having a hard time coming up with the solution, so I'm soliciting your help! The funny thing is that I think the answer must be easy, but because I've been looking at it for so long I can't seem to get out that 'loop' if you know what I mean!
ok here goes. I'm building a database of products for a small company. The challenging part is that some products are priced each, some have ranges (1-9 is $10.00, 10 and up is $8.00), and some have price breaks (50=$1.79 each, 100=$1.65 each, and so on). What makes it even more challenging are that many of the products have the same pricing structure. For example they sell 60 different banners that all carry the same price break structure.
So essentially it is a many to many relationship.
I originally set up a table PRICE, and it's fields were PriceID (prim key), PriceGroup, Quantity, Price. PriceGroup would be a number that related a group of prices together... for instance...
1_1_50_$1.79
2_1_100_$1.65
3_1_300_$1.50
4_1_500_$1.38
5_1_1000_$1.20
6_2_each_$19.95
etc...
Then in table Products I would relate each product to a PriceGroup.
But I've just learned that I can't relate those two tables b/c PriceGroup isn't a unique identifier. Hmmm...
The only other way I can think of doing it is with a linking table, and each product would be related to the PriceID. But that means if a product has 5 price breaks, it would be in the table 5 times. And if there are 60 products with the same price break structure, well... do the math. And I'd have to update each product with the same pricing structure seperately instead of just updating one price structure.
Any suggestions? Remember, I'm new at this so be nice!
Nicole