ERD for Warehouse?
 

Hi all, I am currently designing a database for a warehouse but I cannot get the ERD Wright. The entities which I am using are( Warehouse, order,customer, suppliers, products) these are my tables but after handing in my assignment, it has been returned to me and been tolled there is a table missing. The only thing which I could think of will be (Delivery) table but I am not sure. Any help will be deeply appreciated.

my Current ERD relationships as fallows:

Warehouse-> could have many -> orders
Warehouse -> could have many-> Suppliers
Suppliers -> could supply many-> Products
Orders -> could have many -> Product
Customer -> could have many -> Orders

I came up with these. hope someone could help me to get the ERD Wright for this assignment.

Re: ERD for Warehouse?
 

You need to provide requerements as above description is not valid

Re: ERD for Warehouse?
 

Dear peter_budo
Thank you for your reply, please find attached my ERD diagram but I have been told that it should be another table named (Stock) but I am not sure whither that’s necessary or not.
Thanks for your help.

Re: ERD for Warehouse?
 

hi,
if you design an entity relationship model (ERM) there are only entities and relationships. There aren't any "tables" there. Once you might map your ERM into relational model, which consists of tables (relations) only.

Your shown relationship ' (ONE) Warehouse-> could have many -> orders' expresses the trivial fact that a warehouse has many orders. It is already clear that those orders belong to a sole warehouse because your given task is headlined by "Design a ERM datamodel for a warehouse". Thus, we should omit the entity warehouse.

Your warehouse datamodel may have these entities: products, customers, orders, orderlines, suppliers, stock (to record products on stock)

where the following relationships can be found:

customers ------ one-to-many ----<- orders

orders ----- one-to-many -----<- orderlines

products ----- one-to-many ----<- orderlines

products ->---- many-to-many ----<- suppliers

products ----- one-to-many ----<- Stock

If you are to design the relationship between suppliers and products more detailed in an analogous way to customers/orders, you have to adopt similar entities such us purchase and purchase_orderline.

You also may have a look at http://www.databaseanswers.org/data_models/index.htm.

brs, cliff

Re: ERD for Warehouse?
 

Dear tesuji

Thank you for your reply to my post.

Please find attached my ERD and Data Dictionary I would be very grateful if you could correct any Errors I have made especially with Primary, Foreign and composed key.

Thanks for your help

BTW thanks for pointing out the website.

HB25

Re: ERD for Warehouse?
 

hi

i ve just checked your erd und dd:

1. items must have primary key, e.g. foreign key order_id + item_nb (1,2,3..)
item_nb is necessary, if a certain order contains more then 1 item.

2. relationship between suppliers and products should be many-to-many, that is a certain product could be delivered by many suppliers and a certain suppliers could deliver many products.

3. data type of qty should be integer.

4. I would drop entity warehouse, as already stated.


further on, your erd seems to be ok, especially all crowfeet are placed correctly.

brs, cliff

Re: ERD for Warehouse?
 

Thank you tesuji you have been very helpful and solved my problem.

Kind Regards
HB25