I developped 2 years ago a back office for helping a logistic company to manage the deliveries. At this time, the company only had one "partner" (let's call it Partner A).

The main table is delivery_note.

I tried to design the database to be the more generic as possible. So I decided to put all the partner related fields in a dedicated table.

But two years later, we have 5 partners (and more are incoming) and I don't like to add a new FK in delivery_note each time we add a new partner.

The relation between a delivery_note and a partner is 1 1 (optional). In practice a delivery_note can be linked to only one partner.

The schema looks like:

enter image description here

Do you know any pattern or way to design a more flexible schema to handle this case ?

Thank you

  • What is the relationship between DELIVERY_NOTE and "Partner"? – Michael Kutz Dec 29 '19 at 15:26
  • Sounds like the relationship is backward. That is, the partner note should have a link to delivery_note.id. Question -- Is there exactly one of the partner notes, not all 3 all the time? Or some other frequency? And is that where you need flexibility? But what do you mean by "scaling"? – Rick James Dec 29 '19 at 16:20
  • @RickJames I edited the post. In practice a delivery note can have only one Partner linked. It's not really a Partner but more like "an extra group of fields related to the partner". That's why I wanted dedicated tables because the original design for delivery_note is generic enough to handle the rest of use cases. "Scaling" is maybne not the appropriate term. I was wondering if I can avoid creating new table + a FK when I add a new Partner that needs to store its own field (that are not present in the generic model delivery_note) – Adrien G Dec 29 '19 at 17:17
  • @MichaelKutz 1 to 1 optional – Adrien G Dec 29 '19 at 17:18
  • Are "partners" like UPS/Fedex/DHL?? – Rick James Jan 6 at 16:24

I find your design a bit backword, can a delivery_note_partner_A exist without a corresponding delivery_note?

Assuming that is not the case, I would suggest:

CREATE TABLE delivery_note
( delivery_note_id ... NOT NULL PRIMARY KEY
, ...

Depending on how much processing of the notes for each partner you are going to do, you may consider something like:

CREATE TABLE delivery_note_partner
( delivery_note_partner_id ... NOT NULL PRIMARY KEY
, delivery_note_id ... NOT NULL REFERENCES delivery_note (delivery_note_id)
, delivery_note JSON NOT NULL

Whether the partner should reside in the JSON doc or as an attribute is a decision to make

EDIT: After some discussion in a separate thread, this is the suggestion I proposed:

-- removed attributes delivery_not_parter_X
CREATE TABLE delivery_note (
 delivery_note_id int(11) NOT NULL AUTO_INCREMENT,
`status` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (delivery_note_id),
UNIQUE KEY `UNIQ_1E21328E551F0F81` (`order_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- sample data with above attributes removed
INSERT INTO `delivery_note` (`delivery_note_id`, `status`, `seller_name`, `first_name`, `last_name`, `email`, `street`, `street_extra`, `post_code`, `city`, `lat`, `lng`, `phone_number`, `phone_number2`, `residential_type`, `floor`, `elevator`, `service_type`, `order_number`, `order_picker_name`, `order_flow_origin`, `delivery_day`, `delivery_hour_start`, `delivery_hour_end`, `observation`, `internal_observation`, `created_at`, `updated_at`, `delivered_at`, `antsroute_plan_order_id`, `antsroute_customer_id`, `antsroute_route_id`, `delivery_dday`) VALUES
(1, 'delivered',    'Sauvage',  'Adrien',   'Thibault', 'spotier@wanadoo.fr',   '16 rue des violettes', NULL,   '77500',    'Chelles',  48.89320500,    2.59495500, '0476134831',   NULL,   'house',    'fourth',   0,  'comfort',  '32027253', NULL,   'store_order',  '2020-01-02 00:46:52',  '2019-12-30 15:23:47',  '2019-12-30 16:45:03',  NULL,   NULL,   '2019-12-30 12:11:31',  '2019-12-30 12:11:31',  NULL,   93004,  8782,   NULL,   0),
-- new table
CREATE TABLE delivery_note_partner 
( delivery_note_partner_id int(11) NOT NULL AUTO_INCREMENT
, delivery_note_id int(11) NOT NULL
, delivery_note JSON NOT NULL
  -- example of generated column, for indexing purposes
, payment_method varchar(20) generated always as (delivery_note ->> '$.payment_method')
          PRIMARY KEY (delivery_note_partner_id)
          UNIQUE (delivery_note_id)
          FOREIGN KEY (delivery_note_id)
          REFERENCES delivery_note (delivery_note_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Since MySQL does not support indexes on JSON, this is an example of an index for attributes that are used in predicates

CREATE INDEX X01_DELIVERY_NOTE_PARTNER ON delivery_note_partner (payment_method);

-- some migrated sample data for new table
INSERT INTO delivery_note_partner (delivery_note_id, delivery_note)
VALUES (1, '{ "payment_method":"credit_card", "total_price_amount": 6500, "total_price_currency":"EUR" }')
    ,  (12, '{ "client_account_number":"ACC605739261" }')
    ,  (17, '{ "sum_owed_amount":7500, "sum_owed_currency":"EUR", "delivery_price_amount":2500, "delivery_price_currency":"EUR" }');
  • First of all thank for your answer. It is indeed more elegant and simpler. I confirm that a delivery_note_partner_X cannot exists without a corresponding delivery_note. Tell me if I understand correctly: you suggest to store the "extra" partner related fields in a json document. I'm not sure about the second proposition, what do you mean by "attribute" ? – Adrien G Dec 29 '19 at 21:37
  • Yes, if the attributes are not heavily used in joins, etc, but merely stored, you may consider storing them in a JSON document instead. The second proposition is whether the partner attribute (A, B, C, etc) should be part of the JSON document or as an ordinary column of the row – Lennart Dec 29 '19 at 22:05
  • They are stored and, depending the partner, one attribute can be used in a WHERE clause (I don’t know if I can use a JSON attribute in a where clause). For the 2nd proposition it looks like the delivery_note_partner will become a table to store all partners related fields without distinction. – Adrien G Dec 29 '19 at 23:21
  • 1
    You can find some examples here:mysqltutorial.org/mysql-json – Lennart Dec 29 '19 at 23:25
  • Thanks for the link. So the second proposition consists in creating a JSON property per partner in the delivery_note_table ? – Adrien G Dec 30 '19 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.