CODEWITHSUNDEEP
mysqlsqldatabase-designrelational-databasedatabase-schema
user2707590
user2707590

Reputation: 1126

Best practices about database relation and foreign keys

Assume that a User can ask for a Service and many Providers can make an Offer. The User will then choose one Offer and make a Transaction for it.

Here are the tables:

User:
-id
-name
-address

Service:
-id
-userId
-name
-description

Provider:
-id
-name
-url

Offer:
-id
-serviceId
-providerId
-price
-details
-transactionId

Transaction:
-id
-date
-status (completed, pending etc)
-method (paypal, direct credit card etc)

Ok all the tables have links and we can join to find anything. But my question is: Does it makes sense to store the buyerId (user) and the sellerId(provider) in the transaction even though I can join the tables to get the buyerId and sellerId?

Upvotes: 0

Views: 984

Answers (2)

Filburt
Filburt

Reputation: 18062

I'd suggest a different schema:

User (id, name, address)

Service (id, name, description)

Provider (id, name, url)

Offer (id, userId, serviceId, providerId, price, details)

Transaction (id, offerId, date, status, method)

The Offer will bring together User, Service and Provider and a Transaction is a follow-up to an Offer.

Consider that a User will pick from multiple Offers, creating a Transaction only for one Offer.
Also one Provider may propose multiple Offers for the same Service to the same User each time with a reduced price.

Upvotes: 1

Tirma
Tirma

Reputation: 674

Of course it doesnt make sense. If you have a way to make a join and obtain the results, you should never put this additional columns on the table. Because it will be redundant, and may be can cause errors.

If you need great performance in queries that involve Transaction Table, just make good indexes, but (almost) never add unnecesary columns.

Read more here: https://en.wikipedia.org/wiki/Denormalization

Upvotes: 1

Related Questions