Member Avatar for chwong

Hi I am new of ERD design. I have drawn the diagram but for the supertype, subtype and entity associated part, I am not confirm the way I presented is correct or not. I hope to get some advises from you all.

ERD
1. A distribute places an order

2. A distributor will be ranking based on their sales volume per month.
if sales volume = 100
ranking = consultant
if sales volume = 200
ranking = supervisor

3. An order can consist of many product. A product can have many order. In this situation, is it I can generate a associative entity which is call 'ORDERDETAIL' and the attribute = TotalAmount?

ERD1.docx (177.29 KB)
  • 2 Contributors
  • 6 Replies
  • 868 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by chwong

Dani AI

Generated

Good start — the model is headed in the right direction. A few focused suggestions that fill gaps I saw in the thread and make the design more robust in practice.

First, be clear what you mean by TotalAmount on ORDERDETAIL: if you mean the line amount it is a derived value (quantity × unit_price). Do not store derived values unless you need them for performance; prefer a computed column, a view, or calculate in the application/ETL and persist only with strict update logic. Keep quantity and unit_price as the canonical stored attributes and add CHECKs (quantity > 0, price >= 0).

Second, treat rank as a classification derived from aggregated monthly sales rather than a static field on Distributor. Build a monthly-sales aggregate (view or scheduled aggregate table), keep a small lookup table that maps sales ranges to rank codes, and join when you need the rank. If business rules require an audit trail of rank changes or rank carries independent properties (credit limits, permissions), model rank as a first-class entity and keep a rank_history with effective dates.

Third, when mapping supertype/subtypes to tables pick the pattern that fits your usage:

  • Single table with a type discriminator for only a few optional subtype attributes (simpler queries).
  • One supertype table + separate subtype tables (one-to-one PK/FK) when subtypes have many exclusive attributes or different lifecycles (cleaner normalization).
  • One table per concrete subtype only when subtypes never share behavior (rare).

Finally, clarify cardinalities and enforce integrity: each order line must reference a product (FK NOT NULL); a product may have zero order lines. Distributor can exist without orders (min cardinality 0). Enforce FKs, sensible cascade rules, and index foreign keys for performance. If helpful, a small sample DDL or a read-side view for monthly-sales-to-rank can be provided.

Thanks to and for the original diagram and clarifying notes — the design only needs these small adjustments to be production-ready.

Recommended Answers

All 6 Replies

Member Avatar for tesuji

hi

>>> A distribute places an order
is this a customer?

>>> distributor will be ranking based on their sales
are order and sales identical?

>>> An order can consist of many product...
ERM looks like:

distribute? ----< order ----< order_details/item >---- product
(---< is crowfoot)
where order_details is many-to-many relationship which besides the two foreign keys of the related entities may also have additional attributes as for example quantity and item price.

>>> attached docx?
I don't like to open that. You may post pictures/.jpeg or pdf.

-- tesu

Edited by tesuji because: n/a
Member Avatar for chwong

Hi Tesu,

Thanks for your reply. I have attached my attachment in PDF format. Please guide.

1. Distributor is a customer.
2. Order and sales are identical.

3. It is possible RANK is a entity?

Assumptions
•Distributor can (and probably will) be recorded before they have made any orders. (Therefore the minimum cardinality from DISTRIBUTOR to SALESORDER is 0.)
•It is not possible to make a payment unless there is at least one salesorder to be paid for. It does not make sense to make a payment for 0 salesorder. (Therefore the minimum cardinality from PAYMENT to SALESORDER is 1.
. It is possible that a product may contain 0 order. (Therefore the minimum cardinality from ORDERDETAIL to PRODUCT is 0)

ERD1.pdf (239.48 KB)
Member Avatar for tesuji

He he,

You are ERM master! Your ERM is perfect. (I would only complete the other entities and the relationship by their primary keys as you already did for salesorder, also showing foreign keys would look nice (but this depends on your teacher: some don't like to see keys on erm level))

Also supertyp and subtypes are complete, and well set optional/mandatory cardinalities too. On ERM design level it is a good idea to distinguish between those subtypes. Later when you map this design into relational model (RM) you will have to decide what should happen with all those subtypes.

For there is always a one-to-one relationship between super-/subtypes one can mix them together in RM, in principle. If subtype have various attributes and if they are managed relatively independently from each other, they should have their own tables in RM.

As for 3rd point: Rank is an attribute of subtypes. However, if rank is the only attribute of subtypes, you can simply add rank to supertype/table distributor in relational model (I wouldn't do this in ERM). Later, the values of rank can also be in an extra table in relational model, then it would be kind of look-up table.

-- tesu

Edited by tesuji because: n/a
Member Avatar for chwong

Hi Tesu,

Thanks for your guidance. I am really appreciated it.

As you mention, you will not add RANK in supertype table. May I know, how the RANK should be put in order it can be a look-up table in RM?

Regards,
Wong CH

Member Avatar for tesuji

Hi

A look-up table for ranks could look like:

/*
rankNo salesfrom salesto credit
-----------------------------------
0          0        99   no credit 
1        100       499   Consultant
2        500       999   Supervisor
3       1000      4999   Manager
4       5000    999999   Executive  
   
Primary key: rankNo. (Also salesfrom is possible yet not recommendable.)

Usage, for example in subtype-table manager:

create table manager(distributorNo integer not null,rankNo integer not null,
... further_columns ...,
primary key (distributorNo),
foreign key (distributorNo) references distributor(distributorNo),
foreign key (rankNo) references rank(rankNo)); */

Btw, you should allow to copy from your pdf-file!

-- tesu

Edited by tesuji because: n/a
Member Avatar for chwong

Hi Tesu,

thanks for your explanation. I just create the pdf-file using ms office add-on feature, will check it for future. hehehe... sorry for that.

regards,
Wong CH

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.