Mysql – normalize this! allow user-defined ordering but fall back to default order

database-designMySQL

We have vendors, and they can either be company-wide (office_id=null) or office-specific (office_id is set). Company-wide vendors have an order, this is managed through the Company admin:

TABLE vendor
 - vendor_id (pk)
 - office_id (fk, nullable)
 - name
 - order (int)
 - (etc.)

By default all offices inherit the company vendors and their order. But offices are allowed to manage the order of all vendors independently if they wish. If they arrange the order of their Vendors, we create records in vendor_order:

TABLE vendor_order
 - vendor_order_id (pk)
 - vendor_id (fk)
 - office_id (fk)
 - order (int)

We have this query that gets all the vendors for an office (both company and office-specific), and orders them, first by using vendor_order.order if it exists, then falling back to vendor.order. It works fine, but I think it smells a little funny:

-- GET ALL VENDORS FOR OFFICE IN CORRECT ORDER
SELECT `vendor`.`vendor_id`,
       `vendor`.`office_id`,
       `vendor`.`name`,
       `vendor`.`order`,
       Coalesce(`vendor_order`.`order`, `vendor`.`order`) AS `office_order`
FROM `vendor` LEFT OUTER JOIN `vendor_order`
    ON (`vendor`.`vendor_id` = `vendor_order`.`vendor_id` AND `vendor_order`.`office_id` = 2)
WHERE (`vendor`.`office_id` = 2 OR `vendor`.`office_id` IS NULL
    AND (`vendor_order`.`office_id` = 2 OR `vendor_order`.`vendor_order_id` IS NULL))
ORDER  BY `office_order` ASC

QUESTIONS

  • Can this schema be improved/normalized?
  • Should we just use vendor_order table for the company order too?

Best Answer

Can this schema be improved/normalized? Yes, it would be better if all the ordering data was located in the same place.

Should we just use vendor_order table for the company order too? That's a good idea. That would place all ordering information in the same table where it is much easier to work with and maintain. Of course, you would have to create an office entry that would stand for this default "company-wide" office. But that shouldn't be a problem.

You would have to rewrite the query, of course.

As order data would no longer be in the Vendor table, you would also have to join it to the Vendor_order table on that "company-wide" office then left join again on the actual office number.

I've provided a simple sqlfiddle example. Comment out the WHERE clause to see that all vendors are listed for all offices in the correct order.

Should we make a separate table for office vendors? Not necessary. There should be a table for offices, a table for vendors, and a table which defines the default ordering and any special ordering for specific offices. See the example.

Related Question