2

I'm in the early design phase of a project to provide an e-commerce platform that will require several entities to be modelled, products, customers, orders, CMS pages, etc. They will all have a few things in common (ID, creation timestamp, last modified timestamp, etc).

My first thought was the usual one of giving the various tables an ID column that will use the database's mechanism for assigning uniqueness (autoincrement in MySQL, sequences in Postgres, etc) but given they have a few things in common I was considering a design where all that data is kept in a base BusinessObject table and the tables for the other entities use a primary foreign key that references the BusinessObject table.

For example (in pseudocode)

CREATE TABLE BusinessObject (
    id,
    date_created, 
    date_updated, 
    is_deleted,
    // etc
    PRIMARY KEY id AUTOINCREMENT
);

CREATE TABLE Customer (
    id,
    forename,
    surname,
    // etc
    PRIMARY KEY id
    FOREIGN KEY id REFERENCES BusinessObject.id
);

CREATE TABLE Product (
    id,
    name,
    price,
    description,
    // etc
    PRIMARY KEY id
    FOREIGN KEY id REFERENCES BusinessObject.id
);

and so on.

I can think of a number of advantages to this approach. First, a particular ID always only maps onto one particular object. For example, the id 3 in a system where each table generates its own IDs could refer to a customer, an order or anything else, whereas in the above design, ID 3 will always be an order, because there could never be a customer or product with ID 3. This would make stuff like extrapolating the referenced business object from the URL a lot easier, allowing for simpler routing in the application layer.

However, it also means that every table in the system must join against the BusinessObject table, and I'm worried that this would result in some significant drawbacks. For example the fact that one particular table is going to be involved in nearly all queries may result in degraded performance for that table, or that it might be possible for a row in Customer to reference the same row in BusinessObject as a row in Product, resulting in loss of data integrity unless some additional steps are taken to prevent that.

So basically, what are the pros and cons of a design where a single table provides the identity data for most of the rest of the database? Are such designs fairly common or is it better to just have each table have its own identity source and rely on cleverer application logic to determine the object being referenced?

  • 1
    I don't see how you can easily deduce which concrete object to retrieve from the database if you get ID 42. Unless there is additional 'type information' in the URL, you would need to query all 'derived' BusinessObject tables. And with the additional 'type information', I don't see an advantage for a system-wide unique ID. – Bart van Ingen Schenau Mar 1 '14 at 11:37
  • @BartvanIngenSchenau Am still in the early design stages and am just playing with ideas so hadn't thought of that. I guess I'd need a column in the business object table to indicate the class of the object being queried. – GordonM Mar 1 '14 at 11:45
  • Your BusinessObject table is the way to go, but you might need one for each major type of business object. This sort of thing is absolutely necessary if the data is to be distributed. Keep in mind that you will need two "primary keys": an internal one assigned by your database, and an external one that links to other databases (if the data is distributed). For example, an Employee table would have two keys: internal employee number, and Social Security number (or your equivalent) which identifies human beings uniquely (assuming that one person cannot be two employees in your system). – user251748 Mar 14 '18 at 18:01
2

You are designing your system in the incorrect order. You need to develop your business objects first. I know you are developing your DB first because you are trying to impose patterns on it that belong in your business objects.

You are also making the common mistake of thinking that there can only be one ID because you are thinking in terms of primary keys.

Also, it is not clear to me, that those things are as related as you think they are.

Assuming they are a related pattern, you should define interfaces to express that, IHasBusinessEntityId, ICreatedDate, IModifiedDate. Please do not forget the interface segregation principal. Then perhaps you can aggregate those interfaces into an IAuditable interface?

Explore the patterns in your business objects first, then you can concentrate on making your table structure fast and efficient.

Each of your tables can have the BusinessEntityId (perhaps a guid, maybe ints created by a SEQUENCE), then additionally, the individual tables sequential int identifier to use as the clustered index (guids are not great for clustered indexes). You can then use a SQL UNION to bring back all your IAuditable objects from disparate tables.

I would very much recommend against marrying all these to one table as it will cost you flexibility and performance problems.

This approach will result in clustered index fragmentation, which can drastically slow queries.

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.