Designing a Database From Scratch

Hi all,

I just finished designing my own database - a relatively small one b/c I just wanted to test my schema and design logic. Let me know if you have any ideas to improve it!

(Data entry sure takes a long time… I wonder if there’s a faster way to grab data that I’m sure already exists somewhere in some form on the Internet…)

The schema:

Github folder:

Hello @pearlwong , It looks good :+1: If you are trying to generate a lot of data, you could put everything in Excel, export it to CVS file and then load the data into the tables.

1 Like

Hello!

There are questions about your database:

  1. Your database scheme allows to have from 0 to unlimited menus per shop, right? Because tables with only foreign keys (like menus) usualy used for one-to-many or many-to-many relationships, but your shema says one-to-one between menus and shops.

  2. In the sheme above are arrows for drinks and topping in reverse direction?

  3. Why do you use such composite primary key on menu_items? With that structure every drink will be presented in menu with single predefined topping, is that right? Your current SQL-script fails on inserting values in menu_items table for this reason, see screenshot (only English text is valuable on it).

  4. For SQL-scripts files it would be better to use .sql extension.

Thank you.

Hi,

  1. My schema shows a 1-1 relationship for menu to shop

  2. Arrows are in the correct direction since menu_items are referencing drinks & toppings table respectively.

  3. I need to use a composite primary key (I think) to denote many-to-many relationships. Many menu_items to many drinks and many toppings.
    There are many, many, omg so many combinations of drinks with toppings. However, toppings are not always required. Hence the composite primary keys only take in menu_id and drink_id.

I wrote and ran this on the Postbird app. It’s written PostgreSQL. Not sure what software you are using. My file would not work as regular SQL, please see the README file. Thanks!

Hello!

May be you forget to push some changes in your SQL to git, but all that I said before still actual for postgreSQL/boba at main · pwong09/postgreSQL · GitHub What you want by your schema is not what you get in your SQL.

Let’s see with examples:

  1. Yes, your schema shows 1-1 relationship, but your SQL allows 1-0 relationship. I made a mistake when saying about “many” because did not see UNIQUE constraint on menu.shop_id field. So with current SQL you can have record in shops table but zero records in menus table for this shop and noone stops you. See partial shema below. In this schema shops.menu_id field has NOT NULL attribute (you can’t leave field blank) and UNIQUE (you can not use single menu for multiple shops), so you have to provide one and only one menus record for every shop. This is real 1-1 relationship.
    Boba
    With your schedules - shops relationship similar issue: you can have shops without schedule, and in normal conditions you will have many records in schedules for single shops record. So it is *-1 ralationship. Your SQL confirms that: you insert multiple rows for single shop_id:
 INSERT INTO schedules VALUES 
	(1, 'Mon', '11:30', '02:30'),
  	(1, 'Tue', '11:30', '02:30'),
  	(1, 'Wed', '11:30', '02:30'),
  	(1, 'Thu', '11:30', '02:30'),
  	(1, 'Fri', '11:30', '02:30'),
  	(1, 'Sat', '11:30', '02:30'),
  	(1, 'Sun', '11:30', '02:30');
  1. In normal situation foreign key references primary key. With your arrows direction your primary keys (from drinks and topings) reference regular fields (in menu_items). But in your SQL, the references have another direction:
 CREATE TABLE menu_items (
   menu_id integer REFERENCES menus(menu_id),
   drink_id integer REFERENCES drinks(drink_id),
   topping_id integer REFERENCES toppings(topping_id),
   PRIMARY KEY (menu_id, drink_id)
 );

Your fields from menu_items reference drinks and toppings and this is right. So something wrong with sheme.

  1. But you can’t have single drink in single menu with more than one topping when using primary key consists of menu_id and drink_id because combination of primary key fields must be unique. So if you have menu with id 1 and drink with id 1 with any topping in your menu_items you can’t have anoher topping with same menu and drink combination. This is the essence of primary key - it is always unique. To allow drink without topping you can allow NULL (it is default value) for topping_id and this is enough.
    But here we have a problem with creating apropriate structure for menu_items. We have to create primary key, but can not use toppings_id in it because it may be NULL. One of the solutions is to use surrogate primary key and unique constraint for combination menu_id, drink_id, topping_id like this:
 CREATE TABLE menu_items (
   menu_item_id SERIAL primary KEY,
   menu_id integer not null REFERENCES menus(menu_id), -- we need explicitly set `not null` because this fields outside of primary key
   drink_id integer not null REFERENCES drinks(drink_id),
   topping_id integer REFERENCES toppings(topping_id),
   UNIQUE (menu_id, drink_id, topping_id)
 );

But because of nature of UNIQUE constraint we can have any number of same drinks without toping in single menu. I need to think more about this case.

  1. And one more issue. In 99% of situation all tables have to use primary key, but your schedules table do not use it. I recommend to use composite primary key of shop_id and day, but there may be variants.

Postbird is only a GUI, real errors are generated by PostgreSQL RDBMS. I have used PostgreSQL 14.0 with DBeaver 21.2.3. But this is not so important, because your SQL did not contain any PostgreSQL specific features. And I don’t understand why you think that your file is not regular SQL, because it is. So I have two variants:

  1. You changed your SQL after publishing to GitHub.
  2. Your IDE for some reasons ignores constraint violation, because current SQL from GitHub throws them.

And about your question about data generation. If you are familar with NodeJS then you can use GitHub - faker-js/faker: Generate massive amounts of fake data in the browser and node.js to generate random data and insert in database with some NodeJS library.

Hope my explanation was clear enough, because my English is not so well.

Hi,
I made a few changes to the file that I think will work now.
I’m not going to pretend I understand every point you brought up :slight_smile: I appreciate you taking the time and diving deep into this. The issues you bring up are valid, but also kind of out of scope for my project - and also I am still very early in learning about databases. So I want to learn more before making this any bigger / more complex.

Please feel free to take whatever is useful from my file and build it out further - if you want to!

Have a good rest of your week!

1 Like

Hello!

Your last changes with special topping None and PRIMARY KEY (menu_id, drink_id, topping_id) on menu_items make sense.

Good work!

Made lots of changes since the Database module Portfolio Project of CS calls for essentially the same thing with a few added objectives. New schema:
https://dbdesigner.page.link/DWgXQcZ1tWev71kLA

New file in boba_database: boba_v2.sql with updated readme file - hopefully it works / the changes are an improvement :crossed_fingers: