-1

I'm designing a relational database that I plan to implement with SQL. I have a use case that I'm working on and seem to be having a bit of trouble thinking through the solution. The design is for an e-commerce order system.

Use Case: The ORDER_DETAILS table contains a deliveryMethod attribute. I then have a SHIPPING_DETAILS table that contains address information and a PICKUP_DETAILS table that contains location, date, and time information for an in-person pickup. When a user places an order, they have the option to have their order shipped to their address or to pick up their order in person. My current thought is to have a shippingId foreign key and pickupId foreign key in the ORDER_DETAILS table. Then, basically run a conditional check on the deliveryMethod attribute and retrieve data from the appropriate table depending on the value of that attribute (either "shipping" or "pickup"). With this thought, however, I would be allowing for null values to be present in the ORDER_DETAILS for either the shippingId or the pickupId attributes. From my understanding, null values are viewed negatively in relational designs. So I'm looking for some feedback on this design. Is this okay? Am I overthinking the nulls? Is there a more efficient way to design this particular schema?

Dylan Terrell
  • 195
  • 2
  • 6
  • Absolutely nothing evil about null values. Don't know where you got that from. Sometimes an issue in data warehouses because users may try to join on the column and don't realise they are excluding parent rows if the foreign key is null. In this case the constraint is that you must have a FK matching the delivery method. Thats *usually* just something handled in app layer. – TomC Sep 03 '18 at 04:13
  • This is the multiple/many/2 FKs to multiple/many/2 tables antipattern for sql/database subtyping/inheritance. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using one variant search for your title & keywords for your tags. See the downvote arrow mouseover text. – philipxy Sep 03 '18 at 20:16
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Sep 03 '18 at 20:17
  • PS Always start design with the simplest possible tables. It happens that in SQL DBMSs we often choose to replace some simple base tables by a base table with nulls that is their left join. This gives fewer tables & constraints (which are also ill-supported so to be avoided) but more complicated tables & perversely complicated querying (because of SQL 3VL & nulls). Time to read a textbook on information modeling, the relational model & database design. – philipxy Sep 03 '18 at 20:35

1 Answers1

1

If I understand your problem correctly,

  1. The cardinality of the relationship of ORDER to SHIPPING is 1 ---> (0, 1)
  2. The cardinality of the relationship of ORDER to PICKUP is 1 ---> (0, 1)
  3. An ORDER MUST have either a SHIPPING or a PICKUP, but not both.

To enforce the constraint (#3) you could define a functional constraint in the database. That gets into interesting stuff.

Anyway, like you say, you could make columns in ORDER that are FKs to the SHIPPING or PICKUP tables, but both of those are nullable. I don't think null FKs are evil or anything, but they do get messy especially if you had a whole bunch of delivery methods and not just two.

If you don't like the nulls, you could have separate association tables: (1) ORDER_DELIVERY that has just an order_id and an delivery_id, each are FKs to the respective tables, and (2) ORDER_PICKUP, also a two column table. In each case the primary key would be order_id. Now there are no nulls: the orders with delivery are in the ORDER_DELIVERY table and the orders with pickup are in ORDER_PICKUP.

Of course there's a tradeoff, as maintaining the constraint that there be exactly one and only one delivery method is not a consistency check across tables.

Another idea is to make the delivery and pickup details be JSON fields. Here you are doing more work on the application side, enforcing constraints programmatically, but you won't have nulls.

I wish I could say that there was a slam-dunk go-to design pattern here, but I don't see one. Personally with only two types of delivery methods, I would not shy from having nulls (as I'm not a purist). But I do love it when the database does the work, so....

(Oh, the answer to the question "are you over thinking things?" is no, this thinking is really good!)

Ray Toal
  • 86,166
  • 18
  • 182
  • 232
  • Great feedback! I'm thinking about going the non-purist route with this one. Although the JSON fields aren't a bad option either. Thank you! – Dylan Terrell Sep 03 '18 at 03:57
  • The "slam-dunk go-to" is to use a DBMS that supports simply declaring these constraints without worrying about nulls and without having to enforce them (the constraints) in application code : shark.armchair.mb.ca/~erwin – Erwin Smout Sep 03 '18 at 08:12