Databases Portfolio Project

The portfolio project for CS103: Databases is to make a complete database using PostgreSQL. I made an online store database for my project and you can see the database, sql, and schema diagram on github. Thanks for any feedback!

Hi!

There are some strange decisions and differences between diagram and SQL:

  1. There is a phone_numbers table in the diagram, but not in SQL.
  2. In the diagram, storeaddresses is a 1-to-1 relationship, but the created tables allow for an n-to-1 relationship because store.address_id is not unique. See the relationship customersaddresses.
  3. Use the same names for fields with the same meaning. For example, items.count and order_items.quantity. Why not items.quantity instead of items.count?
  4. Why are you using a totals table instead of including these fields in orders?
  5. And with the totals table, what is the purpose of the total_id primary key? Avoid meaningless and surrogate primary keys. You can just use totals.order_id as the primary key.

Good work!

Hi, thanks for your reply, I really appreciate your feedback!

  1. I initially made 7 tables, which are the first seven statements in the sql. I thought adding a store_hours and phone_numbers table would normalize the database more by removing common data or comma separated data and placing it in another table (e.g. phone numbers were in the store and customers tables and store hours were comma separated in the store table). I deleted or changed these columns, which are statements at lines 316, 319, and 328 in the sql and I made the phone_id column a foreign key at lines 325 and 331. The create table statements are right above that at lines 297 and 306 and they are followed by insert statements. I decided to add these tables later so they are near the end of the file.

  2. I thought I could make two different relationships between addresses and store and addresses and customers because they are different tables and if there was another store it would have to have a different address, whereas customers can have the same address. The difference in the diagram is to show how the tables are being used and the address_id column is not unique in the store table because there is only one store (i.e. the database is for one store) and the purpose of the table was just to provide information about the store. It is not a main table like the orders, items, orders_items, totals and customers tables. Since a one-to-one relationship requires a unique key, I could add that to the column. Thanks for noticing this!

  3. The count column in items is how much stock there is for an item and the quantity column in orders_items is how many of an item the customer purchased. These are slightly different meanings although they might seem similar in the tables.

  4. I made a totals table because all of the columns relate to the cost of the order and not the order itself, whereas the order date, customer, and ship date relate to the order itself. This normalizes the database if you consider placing these columns in the orders table.

  5. For the primary key in totals, I wondered if I needed one since the order_id was unique, but order_id is the primary key for orders, so I thought two tables cannot have the same primary key and I decided to keep the total_id primary key.

Thanks again for your feedback and suggestions!