Database Design: Circular dependency

views:

429

answers:

+3 Q:

Database Design: Circular dependency

Imagine the following database:

Table 'companies' has fields id, name and flagship_product_id. Table 'products' have fields id, name and company_id.

A company must have a flagship product (1:1 relationship) and all products have one company (1:N relationship).

When using a storage engine such as MyISM, there shouldn't be any problem with the above scenario, but when using an engine such as InnoDB, problems result when INSERTing new data.

What is a good solution except allowing a NULL relationship for the initial INSERT?

To summarize, A company must have one flagship product.

I don't know that particular database engine, but search for a way to temporarily suspend the data consistency checks or referential integrity during your atomic insert and update operations.

uosɐſ 2009-09-07 01:55:58

On MySQL it's `SET FOREIGN_KEY_CHECKS=0`, but is this the only way?

LiraNuna 2009-09-07 02:01:07

I'm pretty sure that's route, if you stick to that design. If you did this only for the duration of the operation, why would that be undesirable? I think I know what's bugging you about it - you want it to be cleaner. Unfortunately, I think that relational databases are not good at expressing that kind of relationship.

uosɐſ 2009-09-07 02:05:50

Some things have to be enforced in the application layer. :-)

uosɐſ 2009-09-07 02:07:43

I think that's my only choice then, I need to enforce that relationship and I can't find any other solution. I chose this one because it's the only one that allows me to keep a sane relationship as opposed to "flags" where program error can break stuff.

LiraNuna 2009-09-08 19:10:44

There isn't anything sane about this... As a general rule, if you have to suspend the checks, then your model is broken. I'm not saying it won't work, but once you suspend the check for the insert, there's nothing forcing that field to be set to anything... you have the same program issues that you have if you set a flag in your product table.

David 2009-09-09 03:03:16

You restore the checks at the end of the operation, failing if the data isn't finally consistent - and other such operations continue to be individually consistent. Should be fine. RDBMS is a powerful tool, but it won't have a clean primitive for every application-level situation. You might just consider this an optimization of sorts, pushed down from the application level.

uosɐſ 2009-09-09 15:24:19

This is one of those things that's better done in the application code. It's a very strange system that would set up the company and the products in a single transaction!The problem is that you set up a new company- and suspend the check to do it. Then, you have to go in and create the products right away, before you can associate the "flagship product". The whole time, you have to leave the rules in a suspended state.

David 2009-09-09 16:07:23

I agree, in that scenario suspending the checks is a bad idea. Suspending integrity should only be done for the duration of the transaction - not left open. In that case, the allowing nulls would be much better.

uosɐſ 2009-09-09 16:14:05

This is the worst thing to resort to.

OMG Ponies 2009-09-09 17:32:26

+2 A:

why not put a flagship product field into the products table as a boolean... you could index that and companyid and have a pretty quick lookup

David 2009-09-07 01:58:45

An index doesn't have any impact on business rules - who voted for this?!

OMG Ponies 2009-09-07 02:09:01

+5 A:

You're either going to have to allow NULLs in flagship_product or reconsider how you model this situation. Consider putting flagship_product as a boolean field on product instead. Then you don't have a circular dependency. Or have a product_type field on product that might have values like FLAGSHIP or NORMAL or OBSOLETE or whatever. Of course you have to enforce that but in the past I've found it a cleaner solution to this kind of problem.

cletus 2009-09-07 01:59:27

You could easily take this one step further and have a PRODUCT_ATTRIBUTES table that lists the attributes associated with a product -- one of which could be 'FLAGSHIP'. This allows for multiple attributes if required.

Andrew 2009-09-07 02:02:23

But then it means there could be several flagship products for one company, which is not desired.

LiraNuna 2009-09-07 02:02:46

Like I said, it has to be enforced.

cletus 2009-09-07 02:05:25

How would you enforce it? Check constraints won't work, nor a unique key.

OMG Ponies 2009-09-07 02:21:19

You enforce it at the application level (my preference) or with triggers on the table.

cletus 2009-09-07 02:23:36

But it's a simple relationship to model - check my answer for an example. Application level can have issues if it is a desktop application.

OMG Ponies 2009-09-07 02:27:42

@rexem: it's a valid solution but as noted that doesn't enforce that a company has a flagship product, only that it doesn't have 2+.

cletus 2009-09-07 02:34:28

@cletus: That is an incorrect assumption. The relationship is only that one exist for a company when defined.

OMG Ponies 2009-09-07 02:37:51

Otherwise, the first product added for a company would have to be logged as the flagship and then updated when the flagship was actually added.

OMG Ponies 2009-09-07 02:44:03

@rexem: You could enforce with a unique key on the company_id and flagship_product fields. But I agree with cletus that it would be better enforced at the application level.

Jeff Hornby 2009-09-07 03:26:10

@Jeff: You can't put a unique key on the `COMPANY_ID` and `FLAGSHIP` - then you'd need different FLAGSHIP values for every COMPANY_ID.

OMG Ponies 2009-09-07 04:27:05

+1 A:

I recommend using the following data model:

COMPANIES

COMPANY_ID pk

PRODUCTS

PRODUCT_ID (pk)
COMPANY_ID (fk)

FLAGSHIP_PRODUCTS

COMPANY_ID (pk, fk)
PRODUCT_ID (fk)

Creating a FLAGSHIP column in the PRODUCTS table will not ensure that only one product is the flagship product for the given company because:

A unique key on the FLAGSHIP column requires the values to be different from each other
A check constraint is only a list of acceptable values

OMG Ponies 2009-09-07 02:07:06

Will that complicate queries and hurt performance?

LiraNuna 2009-09-07 02:09:19

Is it good that bad data comes back fast?

OMG Ponies 2009-09-07 02:10:03

This model requires `UNIQUE(company_id, product_id)` on `flagship_products`, right? So it's basically a 1:1 pivot table (weird...).

strager 2009-09-07 02:13:14

@strager: Because the pk is `COMPANY_ID`, you could only ever have one entry for a company - there's no need for a unique key on `PRODUCT_ID`

OMG Ponies 2009-09-07 02:16:02

How can I **enforce** that a company have a flagship product, then?

LiraNuna 2009-09-07 02:25:47

@rexem, Ah, true. @LiraNuna, No idea. This solution only enforces one or none. Hmm...

strager 2009-09-07 02:27:52

The relationship would be enforced when a user attempts to add a product indicated to be the flagship product when one already exists for that company. The primary key would stop duplicates of the `COMPANY_ID` value.

OMG Ponies 2009-09-07 02:29:27

If one does not already exist, it is added.

OMG Ponies 2009-09-07 02:30:01

@remex, But that adding isn't enforced. You could have a company without a flagship product. That isn't what @LiraNuna wants.

strager 2009-09-07 02:51:29

And it is better to assume all products are the flagship unless otherwise specified? That's what defaulting the value will do, without the ability to ensure one exists. What about companies that don't have any products?

OMG Ponies 2009-09-07 03:04:06

I would expect the flagship product to be exposed/highlighted in the application if it is so important.

OMG Ponies 2009-09-07 03:06:22

It's still a circular relationship... you've just added another table to the mix.

David 2009-09-07 03:56:53

The "extra" table enforces the business rule that there can be only one flagship product for a company while allowing for a company to be added without a product existing. Coming from the poster who recommended an index, why do you feel the need to comment on something you know nothing about?

OMG Ponies 2009-09-07 04:16:32

I just realized this has the exact same effect as having the `flagship_product_id` `NULL`able. Right?

strager 2009-09-07 17:14:54

@strager: Assuming you mean `COMPANY.FLAGSHIP_PRODUCT_ID`, yes - that column would have to allow `NULL` when creating a company given the business rules. But that would be better than having an indicator column in the `PRODUCTS` table - besides wasting space, there's no way to enforce the one flagship product rule.

OMG Ponies 2009-09-07 17:33:40

I agree completely that this forces the 1-1 relationship, but it doesn't require that there be a flagship product in the first place- that has to be done at the application level. No matter what you do, you can't get away from some application checking!

David 2009-09-09 16:14:30

Never said it didn't - have all the validation you want in the application, but the data model shouldn't rely on the application validation to enforce data integrity.

OMG Ponies 2009-09-09 17:31:22

+1 A:

The only products that are smart and powerful enough to deal with such situations correctly are systems that fully embrace/implement the concept of Multiple Assignment.

There is not a single SQL system plays in that league.

EDIT

SQL systems have deferred constraint checking, but using that can get messy.

2009-09-08 16:48:11

Here's an outline of a possible work-around. I'm not sure how high on the Kludge scale this fits, but it's up there.

Create Database
Create clients and products tables
Insert a placeholder or "dummy" row in each, configured to reference each other
Establish FK constraints between the tables

Thereafter, whenever a client or product is created, IF the proper referenced product/company has not yet been created, you initialized the new item to point to dummy placholder. Next you enter that item, and you complete by updating the first entry.

The upside is, you have absolute referential integrity once your database initialization routine is completed--and you only run that once under presumably very controlled circumstances, so watch it closely and make sure it doesn't fail! The not downside is, you now have an "extra" item in each table cluttering up your system.

Philip Kelley 2009-09-08 17:11:54

Is this better than allowing nulls - null being your invisible reserved placeholder?

uosɐſ 2009-09-09 15:29:06

Referencing a dummy or placeholder row is a (the?) standard alternative to having a NULL foreign key reference. It's like "Null" = we don't know what it is, and "Placholder" = we know we don't have the data, and we are proactively managing the situation with deliberately entered data. The downside is, you have to deliberately mange/code for the placeholder... but then, you'd have to do the same with nulls as well, so why not make it a bit more obvious what's going on?

Philip Kelley 2009-09-09 17:06:39

ansaurus

tags:

views:

answers:

Database Design: Circular dependency

related questions