Improve database schema

[creme332]

Current issues

Store location 🔴

street, city, and district_id in the order table represent the store location where the client is expected to pick up his order. This data is duplicated in multiple orders and is difficult to maintain.

The solution is to create a store table with attributes store_id, street, city, and district_id. Other possible attribtutes: phone_no, ... Also store the latitude and longitude of the store: this information can be used later for plotting store locations on a map.

Store details 🟢

Additional information worth storing: opening and closing hours, phone_no

Nested reviews 🔴

A review can reply to another review and all reviews contain a rating. It does not make sense for a subreview to have a rating.

A solution is to create a comment entity where each review can have a comment. A comment can also reply to another comment. The comment entity will only contain the following attributes: comment_id, parent_comment_id, review_id, ...

Sort product by latest 🟡

it is not possible to sort products by newest first.

A solution is add created_date to product table.

Product stock level 🔴

The stock level of a product is not necessarily the same in all coffee store locations.

Review 🟡

Reviews posted on the same date cannot be sorted by date because of the lack of time information.

• Use datetime instead of date datatype in review table.

To-do

• Create a new ERD
• Create a class diagram
• Update documentation
• Update models accordingly
• Update stock level trigger
• Update tests accordingly
• Update testing database