Looking for Suggestions from the pros
-
I am buiding a site with many products and i have a FARLEY good grasp on the BASIC understanding about asp.net and sql. I am 7 months young to each of the powerful monsters.
I have a layout chosen already for my site design and i know exactly what i am looking for in design, "In other words i do not want how ever is reading this to think that i woke up this morning and desided to waste thier time."
My delima here is how to lay out the sql design ,"in it's simplest form" for one of my products. The product in question is my blind and shutter lines. I might have one style of blind that comes in many styles AND is priced out per size. In other words every so many inches it X amount of dollars more.
I am wanting to know the best way to design this in sql so that i will know how to design my control in VB.NET. *Alot of online blind stores use drop down list to achieve this goal.
Other online companies: 1 WAY:http://www.selectblinds.com , and 2ND WAY http://www.blinds.com
Any help at all would be SO much help. If anyone has a design that they may have used resently that may resemable what i am tring to do would really be great.
Thank you so much ahead of time.
ERIK *NOTE: If at all possiable keep it simple.
Dam again!
-
Erik, I would suggest setting up a table that contains 4 columns: MaxWidth, MaxHeight, ProductID, Price. So for your basic price grid you would enter one row for each "maximum height" and "maximum width" combination (for each product that has different prices). Ie: the top left hand value from your chart would be one row in the table with values: 23, 42, 1 (or whatever the product ID is), $40
Here's the SQL for the table:
CREATE TABLE [dbo].[SizePrice] (
[MaxWidth] [decimal](9, 2) NOT NULL ,
[MaxHeight] [decimal](9, 2) NOT NULL ,
[ProductID] [int] NOT NULL ,
[Price] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SizePrice] WITH NOCHECK ADD
CONSTRAINT [PK_SizePrice] PRIMARY KEY CLUSTERED
(
[MaxWidth],
[MaxHeight]
) ON [PRIMARY]
GO
This query would get you the price based on the selection of width, height and productID from drop-down menus:
Declare @WidthRequested AS decimal(9,2)
Declare @HeightRequested AS decimal(9,2)
Declare @ProductID int
-- hard-coded for example - would be selected from drop-down menu in app
SET @WidthRequested=24
SET @HeightRequested=42
SET @ProductID=1
-- get the proper price for the width, height and product selected
SELECT MIN([Price]) AS Price
FROM [Samples].[dbo].[SizePrice]
WHERE MaxWidth>=@WidthRequested AND MaxHeight>=@HeightRequested AND ProductID=@ProductID
GROUP BY ProductID
-
Thank you so much!
let me play with this and see what i can come up with..
Thank you, Thank you!
Erik
Dam again!
-
Could you please explain the drop down list, I do understand the logic and that is exactly what i am looking for; however, i need a little help with the actual code part.
Thanks Erik
Dam again!
-
There are numerous ways to implement this code, but my perference would be to use a stored procedure. That way you can pass in the values from your app and get the correct results. Create an sp something like the one below and then execute it from your app code, passing in the 3 variables from the drop-down menus:
CREATE PROCEDURE [dbo].[usp_GetPrice]
@WidthRequested decimal(9,2),
@HeightRequested decimal(9,2),
@ProductID int
AS
-- get the proper price for the width, height and product selected
SELECT MIN([Price]) AS Price
FROM [Samples].[dbo].[SizePrice]
WHERE MaxWidth>=@WidthRequested AND MaxHeight>=@HeightRequested AND ProductID=@ProductID GROUP BY ProductID
-
Ok I see now...
Let me work with this..
Thanks again!
Erik
Dam again!
-
One more suggestion. I'd be flexible in my planning. As you look at products, think about how they might be morphed, new options, etc. and plan for them. I've been stuck a few times when we built a tight design, only to have additional requirements come along. When I've done best is when I've planned for some flexibility, even if it is more design time up front.
For example. Pricing. You cna store a base price with the product, but you really want a separate table to handle different types of pricing (wholesale v retail, etc) as well as sales (time based prices), so having a separate table you read makes things much easier.
-
Here is the base table that i am working with, "i am a newbie" ; however, i like the way this table is designed and i believe that it will work for a starter..
HERE IS MY QUESTION....
I would like to casscase out of the product table and form a brand or company TABLES each table holding the information for that company only.
How would i do that?
From the products, to a Table that interjoins to many other tables?
HERE IS a link to my starter tables...
Any Suggestions will be great!
Erik
Dam again!
-
1. If two products have the same max width/height, you can't store them both with the primary key given in the example. It may be better to create a new primary key for the price table.
2. What you store and what you display are two separate subjects. You need to add a Company table to your schema. If the same product can be created by different companies, you will need a bridge table like your department table has or set up your department table have a foreign key to the company table. If the company is part of the definition of a product, then the foreign key belongs in the product table. That's what you need to store the information. When it comes time to display this information in output tables, have a separate output table for each company and select only the products that are owned by that company.
3. If the same product can be associated with more than one company, your price table will have to have a foreign key to the company table because I'm sure they will have different pricing schemes.
I posted a reply earlier and it didn't show up. So I'm re-posting with basically the same information, but in a different format. Hopefully this post takes.
-
Kenneth, Good point, the productID was an afterthought and I forgot to fix the key. Either creating a separate integer key or adding the productID into the key would work (although I try to avoid 3-field composite keys normally). It would still be a good idea to create a unique index on the 3 fields (MaxWidth, MaxHeight, ProductID) to make sure duplicates aren't entered.
Sample code:
CREATE TABLE [dbo].[SizePrice] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MaxWidth] [decimal](9, 2) NOT NULL ,
[MaxHeight] [decimal](9, 2) NOT NULL ,
[ProductID] [int] NOT NULL ,
[Price] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SizePrice] WITH NOCHECK ADD
CONSTRAINT [PK_SizePrice] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [ind_SizePrice_unique] ON [dbo].[SizePrice]([MaxWidth], [MaxHeight], [ProductID]) ON [PRIMARY]
GO
-
I am really greatful for all of the help! I like it so much i think i'll keep going with the flow..
Anyway i am going to work on the tables this weekend and untill then i would like to keep the education going.Ok here is my next one.
For my sepperation of all of the tables can i do it as follow.
as a one to many to ONE seperation table (Like someone directing traffic), and then that table be loaded with all of the company names only.
Next, go from a one (Companys Table) to a one (Company Table) FOR each different company, and then from each COMPANY table branch off with company products table, and at that time use the table from the previous post for each company? (Pease keep in mind that i can reuse the calulation table, but not the prices for the product chosen)
Thanks Alot!
Erik...
Dam again!
-
these threads are not posting
Dam again!
-
Ok i see how they are posting, thet are actually moving to a seperate page..
Dam again!
-
Yup... we sometimes have huge threads and we have no other choice than to have 'em on difference pages. Also helps to see the sponsors more (hey they got to pay for their servers too).
-
A "one to many to ONE seperation table" is what I call a bridge table, others call it a link or linking table. This bridge table will have keys from other tables and the combination of those keys will form the primary key of the bridge table. The only way this table would have a company name field in it is if that is a key field of one of the linking tables. Sometimes the bridge table may have attributes (more fields) but only when the combination of the other tables produces unique additional data.
A "one to one" relationship has basically one valid reason to exist -> space. There are many flavors of this one reason, but I fail to see why you would have "Companys", and "Company" tables.
Why have 1 to 1? 1. Several of your fields might not need to exist, putting them in a child relationship will save space in the main table. 2. When 1 of those other fields exists, others do need to exist as well. Putting them in a separate table allows you to enforce refined business rules. 3. The fields need to exist, but the space they use is extensive and not always needed when looking up information. 4. The space needed to define one row will exceed 8K-132 bytes.
A Company table should describe unique information about each different company. I could see how your department table could have a "not null" foreign key to this Company table. That would mean the information in 1 row of the department table would only be for one company and that company would have to exist in order to add a row to the department table. (A multi-step process if you've already have data in department.) The price table would have a foreign key to both product and company tables. It is NOT a bridge table, because the combination of product and company can have more than one record in this table. You would add the company foreign key to that three-way unique constraint amackay_1 gave you, to make it four-way. (All four fields would also be "not null".) This way two different companies can offer the same product in the same size with a different (or the same.) price, but the only way they could is if there is an entry in the price table for both companies with the same product and same size. Each company can also offer different sizes for the same product. The proc to find the best price will still work if the person buying the product doesn't care which company supplies the product. You may have to get another proc that lists the best price by company, or specify the company and get their best price.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply