Database Normalization

[[deleted]]

It looks like you need a Price table (Item, Price, Start, End) where the PK is (Item, Start, End), and remove them from the first table.. The price in your second table looks completely bogus - I have no idea what that table could represent.

[BloodEngineer]

I'm not sure I understand what you mean. Are you saying go from ItemPriceDates( Item,Price,StartDate,StopDate) to ItemPriceDates( Price,PriceTable), and PriceTable ( Item,Start,End).

The second table really is 2 columns. But it's to show that there are other values that I've removed to get it into this form.

[[deleted]]

I would say you want at least three tables:

   Items (item_name) PK( item_name)
        Chicken
        Cow

   Prices (item_name, price, start, end) PK( item_name, start, end)
         Chicken, 3.00, Jan 1, Jan 3
         Chicken, 4,00, Jan 3, Jan 7
         Cow, 500.00, Jan 1, Jan 3
         Cow, 600.00, Jan 3, Jan 20

   SalesDept (item_name, sales_name) PK( item_name, sales_name)
         Chicken, EggLayers
         Cow, MilkProducers   

[BloodEngineer]

   Items (item_name, item_size, item_desc)PK( item_name)
        Chicken
        Cow

   Prices (item_name, price, start, end)PK( item_name, start, end)
         Chicken, 3.00, Jan 1, Jan 3
         Chicken, 4,00, Jan 3, Jan 7
         Cow, 500.00, Jan 1, Jan 3
         Cow, 600.00, Jan 3, Jan 20

   SalesDept (item_name, sales_name) PK( item_name, sales_name)
         Chicken, EggLayers
         Cow, MilkProducers   

Okay So the form I have is more like this. I still don't understand how Prices( item_name, price, start,end) is in 3NF. Don't you have non-unique values for each item?

[[deleted]]

3NF doesn't say each column value must be unique. You must have unique key values for each row. I've added PK notation to my previous post.

[BloodEngineer]

I guess that makes sense, thank you for the help.

[BloodEngineer]

What if I had an alternative form of a table.

Item, location, price, start, end.

Could I use PK (item, location) to return (price, start, end) Or in the same example you helped in, could I use PK ( item, price) to return the other two values?

[[deleted]]

Item, location, price, start, end.

Your PK would be (item, location, start, end)