CODEWITHSUNDEEP
database-designorders
Flipper
Flipper

Reputation: 2609

Best Database Structure for Orders

I am torn in between two ways of structuring my database for processing orders. I am not sure if one way will be faster than another. If they are going to be equal then it probably should not matter, right?

Here is option #1.

orders
-------
id
timestamp
userID
cartID
reviewed
approved
reviewBy
reviewTimestamp
reviewDetails
processed
processedBy
processedTimestamp
processedDetails

Option #2:

orders
-------
id
timestamp
userID
cartID
reviewID
processID


reviews
-------
id
timestamp
status
reviewerID
details


processing
----------
id
timestamp
processorID
details

Thanks guys!

Upvotes: 3

Views: 352

Answers (3)

btreat
btreat

Reputation: 1554

I would go with a normalized model. If reviews and processing are many to one with orders, go with option 2. If reviews and processing are one to one with orders, option 1 may be easier to work with for both reading and writing data (ex. one insert vs three).

Upvotes: 1

IAmTimCorey
IAmTimCorey

Reputation: 16757

I would look not only at speed but also at functionality. Who cares if it is fast if it limits you too much to be useful. For example, what if you want to review an order twice (the first time you reject something maybe)? Or what if you process the order in two parts? Unless there is a business case why you really will never have multiples of any of these, I would suggest your second option.

Also, don't forget the reverse could be true as well. For example, one person might process three orders at once. Or they might approve three orders at once. Maybe these aren't happening now, but you need to evaluate the future. Make sure your database model works for you and your use cases.

Finally, when in doubt, I usually opt for the extensible model. I rarely come across a time when I kick myself for having a database structure that is too normalized (I don't go overboard) but I've come across a number of models that have frustrated me to no end because they are unworkable with the (now changed) use cases they are supposed to support.

As far as speed goes, the more you have joins, the slower it will go. However, we aren't talking about massive speed issues unless your database is incredibly large. Do your indexes properly and you most likely will never notice the difference.

Upvotes: 4

mikerobi
mikerobi

Reputation: 20878

I would got with multiple tables, the difference in performance will be negligible, as long as you create the proper indexes.

Upvotes: 1

Related Questions