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:
- There is a
phone_numbers
table in the diagram, but not in SQL. - In the diagram,
store
→addresses
is a 1-to-1 relationship, but the created tables allow for an n-to-1 relationship becausestore.address_id
is not unique. See the relationshipcustomers
→addresses
. - Use the same names for fields with the same meaning. For example,
items.count
andorder_items.quantity
. Why notitems.quantity
instead ofitems.count
? - Why are you using a
totals
table instead of including these fields inorders
? - And with the
totals
table, what is the purpose of thetotal_id
primary key? Avoid meaningless and surrogate primary keys. You can just usetotals.order_id
as the primary key.
Good work!
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
store_hours
andphone_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 thestore
andcustomers
tables and store hours were comma separated in thestore
table). I deleted or changed these columns, which are statements at lines 316, 319, and 328 in the sql and I made thephone_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. -
I thought I could make two different relationships between
addresses
andstore
andaddresses
andcustomers
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 theaddress_id
column is not unique in thestore
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 theorders
,items
,orders_items
,totals
andcustomers
tables. Since a one-to-one relationship requires a unique key, I could add that to the column. Thanks for noticing this! -
The
count
column initems
is how much stock there is for an item and thequantity
column inorders_items
is how many of an item the customer purchased. These are slightly different meanings although they might seem similar in the tables. -
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 theorders
table. -
For the primary key in
totals
, I wondered if I needed one since theorder_id
was unique, butorder_id
is the primary key fororders
, so I thought two tables cannot have the same primary key and I decided to keep thetotal_id
primary key.
Thanks again for your feedback and suggestions!