CODEWITHSUNDEEP
entity-relationshiperd
karafar
karafar

Reputation: 516

How to record customer purchases?

Simplified ERD

I am creating a database which keeps track of customers, items, transactions, and more. I am having trouble describing the relationship between Customers and Items which is represented by buys. The relationship, buys, represents a Customer purchasing Items at a register. The transaction, buys, may include one item, multiple items, and/or the same item multiple times in a single transaction; additionally, multiple Customers may purchase the same item on separate occasions.

What attributes does the relationship buys need in order to keep track of all the items purchased?

Currently, I am proposing an attribute buys.id; however, it seems a composite key is necessary. The keys could (buys.id, Customer.id, Items.id), yet if a customer were to add the same item twice, then these rules would break. I would also include a date attribute. Additionally, a quantity attribute could be added to relationship.

The relationship could be described like so: Relationship 2

It does appear that this would be able to satisfy all the rules laid forth, but it seems a bit cumbersome.

Is there a way to improve this design?

(I need a refresher on this stuff if anybody knows a good book or video)

EDIT:

This answer from another post could answer this question.

I'm using MariaDB and python.

Upvotes: 1

Views: 267

Answers (1)

Nathan
Nathan

Reputation: 4067

Making your model bigger should always be something to be wary of, but in this case you may be missing a concept: order lines.

Since you didn't specify a stack in your question, I'll plug in this python class from a somewhat popular project.

I'll also advise anyone reading this to always, always look on popular open-source projects how they did it if there is even a remote chance you are working on something that has been done before when hitting a wall (perhaps before, but never stop thinking how could you make it simpler!)!

Not only its a great way to see how people usually model stuff and use a shared/common language but you may learn some pitfalls and problems you may run into.

Upvotes: 2

Related Questions