3 ms·

I know it's just an example, but why aren't these tables normalized better anyway? None of them should have columns for the PK of another table. The relationsh

I know it's just an example, but why aren't these tables normalized better anyway? None of them should have columns for the PK of another table. The relationships between them should be represented by bridge tables. Really there's only one relationship here which is product orders. Why have the expense of a join in your select queries when you can just associate them properly during the insert in the first place? Then you can simply select limit 1 from the bridge table and only have one scan on the customer table for the whole query.

thaumasiotes 3y ago
> why aren't these tables normalized better anyway? None of them should have columns for the PK of another table. > The relationships between them should be represented by bridge tables. Really there's only one relationship here which is product orders. What? There appears to be a mistake in the Products table, which only allows a product to belong to one category. But the many-to-many relationship between orders and products is already represented in a bridge table, OrderDetails, and the one-to-many relationship between customers and orders is correct. Why would you have a bridge table between customers and orders instead of recording the same information in a column of the Orders table? And... how is your bridge table going to operate without having columns for the PKs of multiple other tables? That's what OrderDetails is doing. What would you change?
- icameron 3y ago
  Good points but what you think about Bob ordering 47.05 worth of Strawberries?
  - thaumasiotes 3y ago
    None of the products have any prices, so there's no such thing as ordering 47.05 worth of strawberries. The OrderDetails table already records how many strawberries were included in Bob's order of July 3rd.
  - sitharus 3y ago
    Maybe Bob makes jam?
- sublinear 3y ago
  > the one-to-many relationship between customers and orders is correct. Why would you have a bridge table between customers and orders instead of recording the same information in a column of the Orders table? Because as you said the relationship is one customer to many orders. > the many-to-many relationship between orders and products is already represented in a bridge table, OrderDetails The relationship between Orders and Products is one-to-many, not many-to-many. Also, OrderDetails is not a bridge table because it has the quantity of Products. Bridge tables should only have the IDs and it's not going to have one row per order nor per customer. It's going to have n rows where n is the number of categories a product in an order belongs to multiplied by the number of products in an order multiplied the number of orders made by a customer multiplied by the number of customers who have made orders. It's a lot of rows, but compresses very well and makes the queries simpler by flattening all these relationships into one. The only relationship that matters for this query is customers and product category, but you can query for any relationship without joins in this bridge table I'm proposing. > That's what OrderDetails is doing. What would you change? OrderDetails is messed up in other ways too. What is "LineItemID"? LineItem should have the quantity and price? That table isn't even defined in the example. OrderDetails is a bad table that needs to go. Your interpretation of what I'm saying is correct. I would add an actual bridge table (not OrderDetails) that relates the customers to the orders. You're correct that OrderDetails, as written, does need to relate an order to all its products, but my point was that you don't need to join to query for all the customers who have made at least one order of a given category. You just need to select where the category ID matches.
  - thaumasiotes 3y ago
    I'd kind of like to respond, but nothing you just said makes any sense. > OrderDetails is messed up in other ways too. What is "LineItemID"? LineItem should have the quantity and price? That table isn't even defined in the example. You could have answered these questions by just reading the example schema. LineItemId is part of the two-column primary key defined for the OrderDetails table. It's not a reference to anything; presumably its purpose is to define the order in which line items appear in an Order. > OrderDetails is a bad table that needs to go. I hope you don't do a lot of code review. Or much coding, for that matter.
- nice-try-fletch 3y ago
  I don't think the one category is a mistake, merely a simplification. Why introduce another table that complicates the example and adds nothing? The article doesn't seem to be about "here's the absolute perfect design for orders and products" and dwelling on those details, well, doesn't really add anything either. :-)
ahoka 3y ago
What I don’t understand is the totally arbitrary cargo cult nvarchar limits. Why 255?
- nice-try-fletch 3y ago
  It's an arbitrary but common one used in SQL Server. If the example used 128, you'd ask "why 128?" If it was 64, 50, 100, or any other number, you'd still ask. What is the right number? String lengths should come from business rules and the data domain but, when those don't exist (or someone is creating a fake table just to show an example), they borrow from other examples or existing schemas. It's clearly not the point of the article to come up with the absolute best and universally satisfying table design for orders.
- kagakuninja 3y ago
  255 is the maximum array size representable with an unsigned byte. It is possible that old relational DBs could optimize arrays with sizes < 256. I remember that the ancient Pascal dialect I used in college had max array sizes of 255 because they used a single byte for the size.
- sgarland 3y ago
  As a sibling comment said, optimizing for size. However, since in ut8mb4 (the standard for Postgres, and modern MySQL) each char may take up to 4 bytes to store, the updated version of this would be VARCHAR(63). If you can guarantee you’ll only store ASCII, you can safely use 255. This is also probably a premature optimization for practically anyone to be fair. Even at 1 billion rows, you’ve only added 1 GiB of overhead, and at that scale that’s a small percentage of the overall size.
magicalhippo 3y ago
The bridge tables will lead to a lot of extra joins in the rest of the application though. Like the typical case of a grid with the order items which has product name and category as well. Unless you mean something very different in which case some concrete examples would be nice.

thaumasiotes 3y ago

> why aren't these tables normalized better anyway? None of them should have columns for the PK of another table. > The relationships between them should be represented by bridge tables. Really there's only one relationship here which is product orders. What? There appears to be a mistake in the Products table, which only allows a product to belong to one category. But the many-to-many relationship between orders and products is already represented in a bridge table, OrderDetails, and the one-to-many relationship between customers and orders is correct. Why would you have a bridge table between customers and orders instead of recording the same information in a column of the Orders table? And... how is your bridge table going to operate without having columns for the PKs of multiple other tables? That's what OrderDetails is doing. What would you change?

icameron 3y ago
Good points but what you think about Bob ordering 47.05 worth of Strawberries?
- thaumasiotes 3y ago
  None of the products have any prices, so there's no such thing as ordering 47.05 worth of strawberries. The OrderDetails table already records how many strawberries were included in Bob's order of July 3rd.
- sitharus 3y ago
  Maybe Bob makes jam?
sublinear 3y ago
> the one-to-many relationship between customers and orders is correct. Why would you have a bridge table between customers and orders instead of recording the same information in a column of the Orders table? Because as you said the relationship is one customer to many orders. > the many-to-many relationship between orders and products is already represented in a bridge table, OrderDetails The relationship between Orders and Products is one-to-many, not many-to-many. Also, OrderDetails is not a bridge table because it has the quantity of Products. Bridge tables should only have the IDs and it's not going to have one row per order nor per customer. It's going to have n rows where n is the number of categories a product in an order belongs to multiplied by the number of products in an order multiplied the number of orders made by a customer multiplied by the number of customers who have made orders. It's a lot of rows, but compresses very well and makes the queries simpler by flattening all these relationships into one. The only relationship that matters for this query is customers and product category, but you can query for any relationship without joins in this bridge table I'm proposing. > That's what OrderDetails is doing. What would you change? OrderDetails is messed up in other ways too. What is "LineItemID"? LineItem should have the quantity and price? That table isn't even defined in the example. OrderDetails is a bad table that needs to go. Your interpretation of what I'm saying is correct. I would add an actual bridge table (not OrderDetails) that relates the customers to the orders. You're correct that OrderDetails, as written, does need to relate an order to all its products, but my point was that you don't need to join to query for all the customers who have made at least one order of a given category. You just need to select where the category ID matches.
- thaumasiotes 3y ago
  I'd kind of like to respond, but nothing you just said makes any sense. > OrderDetails is messed up in other ways too. What is "LineItemID"? LineItem should have the quantity and price? That table isn't even defined in the example. You could have answered these questions by just reading the example schema. LineItemId is part of the two-column primary key defined for the OrderDetails table. It's not a reference to anything; presumably its purpose is to define the order in which line items appear in an Order. > OrderDetails is a bad table that needs to go. I hope you don't do a lot of code review. Or much coding, for that matter.
nice-try-fletch 3y ago
I don't think the one category is a mistake, merely a simplification. Why introduce another table that complicates the example and adds nothing? The article doesn't seem to be about "here's the absolute perfect design for orders and products" and dwelling on those details, well, doesn't really add anything either. :-)

ahoka 3y ago

What I don’t understand is the totally arbitrary cargo cult nvarchar limits. Why 255?

nice-try-fletch 3y ago
It's an arbitrary but common one used in SQL Server. If the example used 128, you'd ask "why 128?" If it was 64, 50, 100, or any other number, you'd still ask. What is the right number? String lengths should come from business rules and the data domain but, when those don't exist (or someone is creating a fake table just to show an example), they borrow from other examples or existing schemas. It's clearly not the point of the article to come up with the absolute best and universally satisfying table design for orders.
kagakuninja 3y ago
255 is the maximum array size representable with an unsigned byte. It is possible that old relational DBs could optimize arrays with sizes < 256. I remember that the ancient Pascal dialect I used in college had max array sizes of 255 because they used a single byte for the size.
sgarland 3y ago
As a sibling comment said, optimizing for size. However, since in ut8mb4 (the standard for Postgres, and modern MySQL) each char may take up to 4 bytes to store, the updated version of this would be VARCHAR(63). If you can guarantee you’ll only store ASCII, you can safely use 255. This is also probably a premature optimization for practically anyone to be fair. Even at 1 billion rows, you’ve only added 1 GiB of overhead, and at that scale that’s a small percentage of the overall size.

magicalhippo 3y ago

The bridge tables will lead to a lot of extra joins in the rest of the application though. Like the typical case of a grid with the order items which has product name and category as well. Unless you mean something very different in which case some concrete examples would be nice.