How to normalize this database...?

Valkrin

So far I have one table that contains inventory from five different stores. There is a field called stores that holds and integer value, this value is used to distinguish between the stores. The problem is that there are certain items in the inventory that are in more than one store. For example, item x may be in stores 1,2, and 3....So that means there are 3 entries for the same item, only pointing to a different store. I've been trying to figure out how to normalize this but it is late and my mind is not working, any help would be appreciated. Thank you.

[deleted]

I assume you also store the item's description etc in that table and you want to prevent storing that three times?

You should probably create three seperate tables for the stores, the products and the relations between stores and products.

jonsof

According to the theory of normalization you have to make a diagram with the entities and relation between them.
Department, Store, Products.

[deleted]

"According to the theory of normalization you have to make a diagram with the entities and relation between them.
Department, Store, Products."

Perhaps a link to a page that explains this theory is more helpfull than just mentioning the fact taht he should make a diagram (which he doesn't know how to do, or he would have done it 🙂 )

jonsof

You may find it if you search for databases in general.
I don't know any url about the particular diagram. I 'll find more info in terms of that issue and i'll be back.

ahundiak

You basically have a many to many relationship between inventory items and stores.

create table item (
item_id integer, // Primary key
item_name ...
);
create table store (
store_id integer, // Primary key
store_name
);
create table item_store (
item_store_id integer,
item_id integer
store_id integer
quantity integer
);
I know the syntax is not correct but you get the idea.

Valkrin

Thank you for your help everyone, I'll see if I can get things working now. I am going create three tables much like ahundiak suggested. Hopefully I can get it to work.