-1

I'm creating a conceptual data model for a simplified web store using Power Designer.

I'm having trouble specifying the relation between an Order and a Receipt. I would like a receipt to only have a part of the order's identifier in its primary key in the logical model (more specifically, only order_id). I am unable to achieve this by tweaking the relationship properties (see the screenshots bellow; the problematic relationship is marked with a green arrow).

Should I simply omit the relation in the conceptual model?

Conceptual data model

enter image description here

Logical data model

enter image description here

EDIT

If perhaps it wasn't clear how I envisioned my tables…

User

username password mail first_name last_name address
hacker123 greenGrass david.norton@gmail.com David Norton West Shire 40, 1240 Neverland
musicman100 SuperPassword john.stewart@gmail.com John Stewart Strange Alley 50, 1250 Outer Space

Product

product_id name description price_per_unit unit_of_measure supply
1 Tooth Brush 100 NULL 5.99 piece 200
2 Super Paste 200 For sparkling smiles 7.99 piece 50

Order

order_id username product_id amount
50 hacker123 1 2
50 hacker123 2 1
51 musicman100 1 5

Receipt

receipt_id order_id
12 50
13 51

EDIT #2

I just realised that I should probably break up Order into two tables! One to track which products are on a particular order, and another to track who placed the order.

Perhaps I could even split the Order table into 3 parts

Order(order_id, order_time)

ProductsPerOrder(order_id, product_id, amount)

OrdersPlaced(order_id, username)

Janez Kuhar
  • 3,705
  • 4
  • 22
  • 45

2 Answers2

1

You have a contradiction... One part says that Order is identified by User+Product+Order; the other says that Order has its own identifier order_id.

I guess the second one is correct, with the usual design that Order has an id.

And you need to change the relationships in the CDM, between Order, and User/Product, to uncheck the Dependent property. These links are just mandatory, not dependent (which would mean that Order is defined relatively to User+Product).

p.s. the same holds for Receipt, which has its own identifier.

pascal
  • 3,287
  • 1
  • 17
  • 35
  • Either order_id is an identifier, and user_id, product_id are redundant in Order primary key (which reminds me of lessons on Second Normal Form from decades ago). Or it's relative to user_id+product_id (more probably, relative to user_id, with a sequential number to keep track of the N-th order from this customer), and you need user_id+product_id in Receipt... – pascal Jul 02 '21 at 09:39
  • 1
    I'm not quite sure what to do with my question now. On the one hand, I've found a way to circumvent poor design, but your answer made me redesign my model, effectively making my original answer obsolete. – Janez Kuhar Jul 02 '21 at 10:18
  • 2
    Leave your question as it is... It can trigger an aha moment for someone else... – pascal Jul 02 '21 at 16:02
0

You can edit the relationship in the logical model!

If you click on a relationship, a Relationship properties dialog appears. There's a tab called Joins. This is where you can specify which columns to refer to with the relationship.

Janez Kuhar
  • 3,705
  • 4
  • 22
  • 45