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!
There are some strange decisions and differences between diagram and SQL:
- There is a
phone_numberstable in the diagram, but not in SQL.
- In the diagram,
addressesis a 1-to-1 relationship, but the created tables allow for an n-to-1 relationship because
store.address_idis not unique. See the relationship
- Use the same names for fields with the same meaning. For example,
order_items.quantity. Why not
- Why are you using a
totalstable instead of including these fields in
- And with the
totalstable, what is the purpose of the
total_idprimary key? Avoid meaningless and surrogate primary keys. You can just use
totals.order_idas the primary key.
Hi, thanks for your reply, I really appreciate your feedback!
I initially made 7 tables, which are the first seven statements in the sql. I thought adding a
phone_numberstable 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
customerstables and store hours were comma separated in the
storetable). I deleted or changed these columns, which are statements at lines 316, 319, and 328 in the sql and I made the
phone_idcolumn 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.
I thought I could make two different relationships between
customersbecause 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_idcolumn is not unique in the
storetable 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
customerstables. Since a one-to-one relationship requires a unique key, I could add that to the column. Thanks for noticing this!
itemsis how much stock there is for an item and the
orders_itemsis how many of an item the customer purchased. These are slightly different meanings although they might seem similar in the tables.
I made a
totalstable 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
For the primary key in
totals, I wondered if I needed one since the
order_idwas unique, but
order_idis the primary key for
orders, so I thought two tables cannot have the same primary key and I decided to keep the
Thanks again for your feedback and suggestions!