CODEWITHSUNDEEP
databasedatabase-designerd
user1352609
user1352609

Reputation: 145

Two types of orders ERD

im new to database design and im trying to create an ERD for a pharmacy that sells products online and also accepts repeat prescriptions orders to customers.

Here is the link to my ERD: https://i.sstatic.net/vmeOD.png

I'm wondering how i could only make use of only one 'payment' and 'card details' entity for both orders, so have i have 2 entities for each order. Would i be able to make a relationship between the ORDER TYPE entity and payment/card details entity.

Any help would be appreciated. thanks

Upvotes: 0

Views: 1959

Answers (2)

Adam Musch
Adam Musch

Reputation: 13628

I'm trying to find a good reason forh aving both Prescription_Order and Product_Order. There should just be an Orders (pluralized because ORDER is a reserved word everywhere) table, where the Customer_Recurring_Order table (with a natural key of references to Customer and Product) breaks out the rules for a customer's recurring orders, regardless of whether they are prescription in nature or not. Your medicine attribute properly belongs as a record in the product table.

Also, the Card Details table should properly be linked to one or more Customer, as I'm pretty sure that card details are functionally dependent on the existence of a customer.

Once you've cleaned those up, you only need one Payment or Card Details entity. A good design principle to remember is that if two entities have all the same attributes, they probably aren't two different entities.

Why didn't you break out shipping information into a separate entity, Order_Shipment. Will all shipments always contain all portions of a customer's order?

Upvotes: 0

vyegorov
vyegorov

Reputation: 22905

You have some flaws in your design though:

  1. Product Order and Order Product. Developers and admins will go crazy remembering which table is used for what.
  2. It is unclear why you have 2 Card Details? Instead of adding Perscription_PaymentID and Product_PaymentID columns to the Card Details do it the other way around – add CardID field to both tables.
  3. It is not clear for me the intention of joining Customer with ORDER TYPE. Consider changing names.

Also, even writing this small response it was so difficult to follow your table/column names, that contain spaces, underscores, mixed-case-names, singular/plural variants.

Please, take a look at this answer and consider using some generic approach in naming your relations, columns and constraints.

Upvotes: 1

Related Questions