table design

[jeo]

I have a design dilemma. I have a Price table with 50 fields (all of them, except for 1 are a yes/no options). If one of those fields is selected as a Yes, then I need to add it into my Total Price calculation. Each variable have 7 pricing criteria: days, # of people, hours, base fee, difficulty factor, and adjustments.
That means 49*7 = 343 fields! Plus a Total price field for each, that's another 49...
That's a lot of fields for 1 table, I think.
I have 6 tables like that for different products.
Rather than having to add all those fields into a table, may be there is a better way of doing it?
Any suggestions would be appreciated!

 

[Navyguy]

Hi Jeo

The first thing that pops into my head is normalization...but not to sure how to tackle that with just Y/N fields.

Perhaps you can reduce your price table into smaller "Like Prices". Eg tblmaintenance, tblcleaning, tbltravel, tblfood, etc. Maybe then you will only need to select a few from each table.

Not knowing what it looks like makes it kind of hard to suggest something, but I hope this is helpful.

 

[Mile-O]

That means 49*7 = 343 fields! Plus a Total price field for each, that's another 49...
That's a lot of fields for 1 table, I think.

Especially when you are restricted to 256 fields per table. Do as NavyGuy suggests and look up normalisation in the archives and on the greater web.

Currently, your database does not meet First Normal Form (1NF) which I've tried to explain in a thread in the FAQ forum.

A database should grow down and not across so.

 

[Mile-O]

If you can, upload an example of your table structure.

 

[jeo]

Thank you! That helped immensely.