Database design: Table design and table for multiple entities

Question

I have a problem designing a few specific tables but I just want to double-check with others. This is a long message since I'm explaining a lot but I include a TLDR at the bottom.

How do you decide what database design works best?

If I'm creating a database for companies selling dishes wholesale - plates, bowls, cups, etc. The purpose of this database is for users to store information about their dishes and for that information to show on the website. This would be a single database where we rely on userIDs for what item belongs to who (not a database per user). Additionally, the view from the seller is that their profile is split by type of dish.

I can think of three different designs for the dishes, ignore the specifics (this is just an example) except for "weight, height, diameter" -> These measurements depend on what type of dish you use.

• Design 1 has a table per dish type since plates won't need the height measurement, measurements are specific to the dish type.
• Design 2 is a single table with the dish types being an attribute, and the measurements are included in that table.
• Design 3 is split into two tables: one with dish information and the other for storing the information.

How would I choose between these designs? I feel like design 2 and design 3 would have missing / zero values for the measurement attributes that don't apply to the dish type.

Additionally, if I make the measurements allow NULL values, would there be any big difference between the three designs, since they'd all have missing/zero values (and that defeats the purpose of trying to avoid them).

Would it be better to just not allow NULL values for measurements with design 1, that way there's some sort of benefit to using it?

Similarly, if I were to go with design 1 where there's a table per dish type, how would I store the orders? This would be an N:M relationship.

• Design A would store every order in a single table, where the DishID references the IDs from the plate, bowl table.
• Design B would have an order table per dish type.

Since a user can have different dish types in their order, is it better to separate them?

My thoughts:

For the first question, I'm leaning towards design 1. Design 1 would result in multiple tables with almost identical attributes except for one or two measurement attributes. I feel like it makes sense considering the initial sorting would be by dish type, but we would also need to search through multiple tables if we're looking for all dishes with a diameter of 6 inches. However, I feel like having the tailored measurements to dish type wouldn't matter if they could accept NULL values.

Design 2 and 3 would result in one/two long tables. But it would still be easy to sort by dish type for the website UI. Design 2 would also search in a single table and design 3 would search in one to two tables only for specific filter results.

Design 1 and 3 would also result in having to delete from multiple tables in case a user deletes their account.

I feel like I'm more drawn to Design 1 but I can also think of more drawbacks (allowing NULLS, searching and deleting over multiple tables, creating the issue for question 2) than the other two.

For the second question, I'm less confident in knowing which one to pick.

• Option A is a single table which makes searching easier but is it possible to make a foreign key reference multiple tables?
• Option B would require searching through multiple tables but there's no issue with a foreign key referencing multiple tables.

TLDR:

If you're creating a database whose main function is to store information, display information, and filter the displayed information, do we prioritize by:

• Reducing the number of tables to search in order to display something (so make a single table)
• Reducing the number of tables to search through in order to delete something (making a single table or making a table per type with tailored attributes)
• The original display method (making a table per type)

Additionally, is it better to create a table where a foreign key references multiple primary keys or have multiple tables where the foreign key references one primary key?

Thank you!

Answer 1

I'd recommend something more like https://dbfiddle.uk/gqIuWHlu

This is postgreSQL but could be adapted to MySQL or whatever.

Here I came up with 4 tables. One for each dish type, one for each dish, one for each attribute type, and one for each dish/attribute value.

One issue with this approach is that each attribute value must be the same type. For your example that could be float but if you have many different attribute value types that makes it more complicated.

CREATE TABLE dish_types (
  id  serial primary key,
  name varchar
);

CREATE TABLE dishes (
  id  serial primary key,
  type bigint,
  foreign key (type) references dish_types(id)
);

CREATE TABLE attributes (
  id serial primary key,
  name varchar,
  unit varchar
);

create table dish_attributes (
  dish_id bigint,
  attr_id bigint,
  foreign key(dish_id) references dishes(id),
  foreign key(attr_id) references attributes(id),
  value float
);

INSERT INTO dish_types (id, name) values 
  (1, 'bowl'),
  (2, 'plate'),
  (3, 'glass');

INSERT INTO attributes (id, name, unit) values 
  (1, 'diameter', 'mm'), 
  (2, 'height', 'mm'), 
  (3, 'volume', 'ml');

INSERT INTO dishes (id, type) VALUES 
(1, 1), (2, 2), (3, 3);

INSERT INTO dish_attributes(dish_id, attr_id, value) values 
(1, 1, 10),
(1, 3, 750),
(2, 1, 8.5), 
(3, 3, 250);

SELECT dish_types.name,  
  string_agg(concat(
    attributes.name, ' ',
    dish_attributes.value, ' ',
    attributes.unit),
    ', '
  ) AS attributes
FROM dishes 
JOIN dish_types ON dishes.type = dish_types.id
JOIN dish_attributes ON dish_attributes.dish_id = dishes.id 
JOIN attributes ON dish_attributes.attr_id = attributes.id 
GROUP BY dishes.id, dish_types.name ;

name	attributes
bowl	diameter 10 mm, volume 750 ml
plate	diameter 8.5 mm
glass	volume 250 ml