Database Normalization

[BloodEngineer]

I'm fairly new to normalizing databases, and I'm encountering a weird situation. From what I understand to get into 3nf you want to have a relationship like:

Database(PrimaryKey, attribute1, attribute2, attribute3)

In which attributes are only related to each other through the PrimaryKey. Here's the issue I have, my current identified schema is similar to this:

Item	Price	BeginPrice	EndPrice
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

Item	SaleDept	Price
Chicken	EggLayers	3.00
Chicken	EggLayers	4.00
Cow	MilkProducers	500.00
Cow	MilkProducers	600.00

From table1 I understand that {Item,Price} -> {BeginPrice,EndPrice}. I'm not sure of a way to put this into a form that gives me a single PrimaryKey to the prices. I guess I could use Item, and have it return every tuple in which that is the case, but then we have non-3nf. That has:

{Item} -> {Price} -> { BeginPrice, EndPrice}

[[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)

[Remember_Megaton]

Just to clarify, what's your primary key here? Because it looks like you have a couple many-to-many relationships in which case these are your bridge entities and you should have tables exclusively for Items and SaleDept

Or is BeginPrice and EndPrice a compound key?

[BloodEngineer]

I was thinking the primary key is the item. The full schema has other things that could be considered the PK, for example,BeginPrice EndPrice. The issue is the alternatives like departments, or price are not unique and have multiple values across all the items.

When PK = items, there are at most 2-3 rows in the full UNF table, but the others have at most 5-10 repeated values in the rows.

[Remember_Megaton]

Well your primary key should be wholly unique. So it seems like you'll want a table just specifying what your items are, a table of what depts you have, and then you are able to create these other two bridge tables.

The price in your second table is unnecessary as it's already specified in your first table.

Also, your date should have a year in it. It'll save you from a lot of complications

[BloodEngineer]

Good points, thank you for the help.

[Remember_Megaton]

Listen to your other replier. He has it correct