Table design/structure help please...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table design/structure help please...

Author

Topic

grumster
Starting Member

3 Posts

Posted - 2006-09-19 : 09:42:28

Hi all

I am developing a site for a client but unfortunately
I havn't much of a clue regarding db/table design.

Scenario:

The client intends to sell window blinds over the internet.
The site has been developed using Asp.Net2 and C# with Server 2005.

I need to develop a price lookup control that has access to 4 parameters:

ManufacturerID (from existing table Products.manufacturerID)
PriceBandID (from existing table Products.PriceBandID)

Width (input by the user)
Drop (input by the user)

The idea being upon button click, the correct price is returned based on those parameters.

The price structure has been given to me in the form of Excel files.

There are around 20 different manufacturers
each with a 7 band (A - G) price matrix.

My question is what would be the most efficient way to represtent
this pricing structure under the current environment?

Will I really need 20 * 7 tables?

Any help, info, advice, pointers, links or comments would be most welcome!

Thanks for your time.

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-19 : 09:53:29

Ummmm...No

Read the hint link in my sig, But I think you want a column for "matrix" (whatever that is) and a column for manufacturer

Post some sample data, and what your expected results of a select should be

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-19 : 09:57:09

Sounds like a choice between 20* 7 tables with one row and column
or
1 table with 20 rows and 7 columns
or
1 table with 20 rows and 2 columns
another table with 7 rows and 2 columns
another table with 20*7 rows and 3 columns

man_id, man_desc
band_id, band_desc
man_id, band_id, price

I would go for the 3rd option so that more bands could be added easily but I suspect it would be easier to go for the second.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

grumster
Starting Member

3 Posts

Posted - 2006-09-19 : 13:51:30

Thanks for the replies.

Great at least I know where to start!

I think I will go for the third option as

man_id, man_desc
band_id, band_desc

tables are existing.

Thanks for your time.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-19 : 19:33:06

quote:
Originally posted by grumster
... but unfortunately I havn't much of a clue regarding db/table design.

You must have an awesome sales pitch. How do you get clients to sign you up to write applications without this pretty fundemental skill?

grumster
Starting Member

3 Posts

Posted - 2006-09-20 : 05:20:50

Ok my mistake - actually I do have a bit more of a clue than I gave myself credit for

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-20 : 21:15:35

Fair enough.
Depending on how flexible you need to be, I recon the best design would be a table that holds all your parameters and the price.

ManufacturerID, PriceBandID, Width, Drop, Price.

This is easily generated from your spreadsheet.

Done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources