Optimise primary key and index settings for multiple link tables

Question

I have this tables stated as below.

1. orders is the main table that will store all the details such as the customerID, merchantID, which riderID it was assign. The order details like total, subTotal, tax and also customer details like address latitude, longitude. It also saves every detail like when the order was accepted, ready, delivered.

CREATE TABLE `orders` (
  `orderID` int UNSIGNED NOT NULL,
  `orderIDHash` varchar(10) NOT NULL,
  `customerID` mediumint UNSIGNED NOT NULL,
  `merchantID` smallint UNSIGNED NOT NULL,
  `branchID` smallint UNSIGNED NOT NULL,
  `riderID` smallint UNSIGNED NOT NULL,
  `orderDateTime` timestamp NOT NULL,
  `subTotal` decimal(10,0) NOT NULL,
  `tax` decimal(10,0) NOT NULL,
  `total` decimal(10,0) NOT NULL,
  `quantity` tinyint NOT NULL,
  `customerAddress` varchar(150) NOT NULL,
  `customerLatitude` float NOT NULL,
  `customerLongitude` float NOT NULL,
  `paymentID` varchar(30) NOT NULL,
  `paymentType` tinyint NOT NULL,
  `instructions` varchar(100) NOT NULL,
  `orderType` tinyint NOT NULL,
  `neworderNotify` tinyint NOT NULL,
  `orderAcceptedDateTime` timestamp NOT NULL,
  `merchantUserAcceptedID` smallint NOT NULL DEFAULT '0',
  `orderAcceptedNotify` tinyint NOT NULL DEFAULT '0',
  `orderReadyDateTime` timestamp NOT NULL,
  `merchantUserReadyID` smallint NOT NULL DEFAULT '0',
  `orderReadyNotify` tinyint NOT NULL DEFAULT '0',
  `orderInDeliveryDateTime` timestamp NOT NULL,
  `orderInDeliveryNotify` tinyint NOT NULL DEFAULT '0',
  `orderPickerOrDeliveryDateTime` timestamp NOT NULL,
  `orderCancelDateTime` timestamp NOT NULL,
  `orderStatus` tinyint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `orders`
  ADD PRIMARY KEY (`orderID`),
  ADD UNIQUE KEY `orderIDHash` (`orderIDHash`),
  ADD KEY `customerID` (`customerID`),
  ADD KEY `merchantID` (`merchantID`),
  ADD KEY `riderID` (`riderID`),
  ADD KEY `branchID` (`branchID`),
  ADD KEY `orderStatus` (`orderStatus`),
  ADD KEY `orderAcceptedNotify` (`orderAcceptedNotify`),
  ADD KEY `orderReadyNotify` (`orderReadyNotify`),
  ADD KEY `orderInDeliveryNotify` (`orderInDeliveryNotify`);


ALTER TABLE `orders`
  MODIFY `orderID` int UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

2. The second table is the ordersLine. The table basically store all the items related to one particular order. Thus its related to the order table above.

CREATE TABLE `orderLine` (
  `orderID` int NOT NULL,
  `itemID` mediumint NOT NULL,
  `itemQuantity` tinyint NOT NULL,
  `itemPrice` decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `orderLine`
  ADD PRIMARY KEY (`orderID`,`itemID`);
COMMIT;

3. CustomerMessageLog is basically for example is the order table above we have this field orderAcceptedNotify which will change its value say default is 0 then to 1 meaning order is accepted by merchant and to 2 when its already notified to the user. Thus we store the message in this log so when user log he can see all the message. There can be different messageID for different type of messages.

CREATE TABLE `customerMessageLog` (
  `orderID` int NOT NULL,
  `customerID` mediumint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE `customerMessageLog`
  ADD PRIMARY KEY (`orderID`,`customerID`,`messageID`);
COMMIT;

4. This table is to store all the notified message to the rider.

CREATE TABLE `riderMessageLog` (
  `orderID` int NOT NULL,
  `riderID` smallint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
ALTER TABLE `riderMessageLog`
  ADD PRIMARY KEY (`orderID`,`riderID`,`messageID`);
COMMIT;

5. This table is to store all the notified message to the merchant.

CREATE TABLE `merchantMessageLog` (
  `orderID` int NOT NULL,
  `merchantID` smallint NOT NULL,
  `branchID` smallint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE `merchantMessageLog`
  ADD PRIMARY KEY (`orderID`,`merchantID`,`branchID`,`messageID`);
COMMIT;

6. This is the table which will store all the orderStatus and also messageID in most of the tables above refer to this table via join.

CREATE TABLE `statusMessage` (
  `statusID` tinyint UNSIGNED NOT NULL,
  `message` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `statusMessage`
--

INSERT INTO `statusMessage` (`statusID`, `message`) VALUES
(0, 'Pending'),
(1, 'Payment Completed'),
(2, 'Accepted'),
(3, 'Completed'),
(4, 'Self Picked Up'),
(5, 'Assigned Rider'),
(6, 'In Delivery'),
(7, 'Delivered');


ALTER TABLE `statusMessage`
  ADD PRIMARY KEY (`statusID`);

So now the challenge is that I have set the orderID to be DDMMYYHHMMSS which I get from the user phone before order is submitted for payment. Why I did this is cause for the payment gateway I will need to orderID.On the other hand I could also set it as auto increment and can also insert all the order and orderLine before payment but if the payment fails then that is another issue how to handled the inserted order and orderLine. So which is most effective method. Also I would like to hash the primary when its shown to user?

Answer 1

customerLongitude float(10,8) chops off about half the world. Simply use FLOAT.

itemPrice float -- Use DECIMAL for monetary values.

Unless a "customer" rarely makes more than one "order", have the customer details in a separate table.

Ditto for "merchant"?

A "customer" makes an "order"; the "order" includes "items" (your "order line"). The point here is that the "items" do not need a link for "customer_id".

Think about what "entities" you have -- customers, merchants, orders, items within an order.

Then think about how they are related. And decide whether they are 1:1, 1:many, or many:many.

• 1 customer makes many orders
• 1 order has many items

Those are handled by having customer_id as a column in orders and order_id as a column in items.

1:1 relations should (usually) be avoided.

many:many requires an extra table with two columns -- namely the ids linking to the two Entities.

That discussion will move you closer to deciding on PRIMARY KEYs. But keep in mind that a PRIMARY KEY is, by definition (in MySQL), a column (or combination of columns) that minimally identifies the rows. Some of the PKs you have suggested do not follow those rules.

Before getting into the rest of the INDEXes, sketch out the desired SELECTs. They will drive what indexes (in addition to the PKs) that you will need.

HashID

For database convenience, you have an AUTO_INCREMENT. For a minor amount of obfuscation, you have added a HashID as some function of the auto_inc. I assume you will set the hash when you create the row.

UNIQUE(HashID) is certainly a way to assure that the row is addressable either way, and you can map between the ids. Having it in the same table (as opposed to an extra table) is probably fine.

I say "minor" because in cryptology the least secure part is the algorithm. You could beef it up with a secret "salt".

If there is some risk of dup hashes, then that can be easily worked around -- DELETE the row, and INSERT a new row to get a new auto_inc id and hash. An occasional loss of an id is not a big deal.