0

First off, I'm new to architecting a DB and I'm looking for help from people who are more experienced and are able to point out gotchas and issues that I might not see.

I'm building out a new DB schema and I'm weighing the benefits of using composite keys for uniqueness vs a single primary key.

Here is an example. An Owner owns things, Things have components, Components have descriptions.

Approach 1 - Composite Key

One way I can architect this is by giving the owner an INT id, OwnerId, and then anything they own a SMALLINT id, OwnedThingId. The result is that each owner has MAX(SMALLINT) things they can own. The other result is that the OwnedThingId's are repeated in the db. For example, the table would have many different OwnedThingId's of 1. Uniqueness is only attainable by composing the OwnedThingId with the OwnerId. In this case the PK would be (OwnerId, OwnedThingId).

Approach1

Approach 2 - Single Key

The other way I can architect this is by making the OwnedThingId bigger, say an INT, and make that field unique throughout the whole table. In this case there would only be one thing with an id of 1, regardless of how many owners there are. The result is that there is a limit of MAX(INT) for all owners. However, the record is now easier to refer to since I only need an OwnedThingId instead of both an OwnerId and OwnedThingId.

Approach2

Approach 1 - Pros & Cons

Pro: I like approach 1 because it has the potential to take less space with smaller keys and because it's more contained. The number of owners in the system doesn't really matter, the OwnedThingId's available to the owners doesn't change. It also mirrors the real world a bit better. i.e. Student id 12 isn't the same student at all the schools ever. Con: Since multiple ids are required to determine uniqueness, there is the potential for a future table to store and need to refer to X number of id's instead of just storing one id.

Approach 2 - Pros & Cons

Pro: Only one field is needed to confer uniqueness. A Record is unique across all owners. There is also less to keep track of, a future table would only need to store and refer to the one unique id.

Con: It can take more space because it uses bigger int values. Each owner that claims an id, reduces the pool of available ids for all owners.

Conclusion

Like I said, I'm leaning towards approach 1, but I get the feeling like I might be shooting myself in the foot as time goes on and the system grows. Thus I am reaching out to those with more experience in architecture than I to inform me of potential gotcha's or reasons why I should consider one over the other.

0

1 Answer 1

1

Your verbal description implies

  • These "Entities": Owner, Thing, Component -- each being a table.
  • These "many-to-many Relations": OwnerThing, ThingComponent -- each being a table

Entities have PRIMARY KEY(id).

A many-to-many table is likely to be no more than

 CREATE TABLE OwnerThing (
     owner_id ...,
     thing_id ...,
     PRIMARY KEY(owner_id, thing_id),
     INDEX(thing_id, owner_id)
 ) ENGINE=InnoDB;

Note that id for a many-to-many table is actually a de-optimizer. Those composite indexes are optimal.

Entities, on the other hand, usually need

PRIMARY KEY(id),
INDEX(...)  -- as needed by the queries

Optimization is determined more by the queries than by the schema. Please provide some typical SELECTs.

More in my Index Cookbook

Relations On the other hand...

If a Thing is owned by only Owner and a Component exists in only one Thing, then you have "one-to-many" relationships. Such should be handled by owner_idinThingsandthing_id` in Components.

4
  • Are you suggesting storing the relationship in a join table? I was planning on nixing the join table and storing the relationship directly on the entity table itself. Mar 27 at 21:30
  • @jeremysawesome - many-to-many requires an extra table; one-to-many does not.
    – Rick James
    Mar 28 at 4:45
  • I was confused by the name "OwnerThing". I added a paragraph.
    – Rick James
    Mar 28 at 4:50
  • Interesting moment... does defining the backward index as unique may provide some profit? Of course this provides insert/update overhead due to excess uniqueness check - but maybe there is a case when the index uniqueness may cause in execution plan change to the more effective one?
    – Akina
    Mar 28 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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