1

At my current workplace there is a common pattern in database design: they don't use foreign keys but they list all corresponding ids in a column like this:

some_table
id  name image_ids
1   a    1,2,3
2   b    4,6,7

images
id url
1  ...
2  ...
...

They store self references in a same manner:

some_table
id  name  some_table_id
1   a     2,3
2   b     1,3
...

They encourage me to use this pattern but it does not feel right for me. I would never design a database like that. I have some counter arguments against it:

  • What if some day I would add some arbitrary data to a self reference? Using this model I would not be able to.
  • It does not ensure referential integrity. I can easily add non-existent ids which will lead to problems
  • Searching through strings is not fast either

I need to justify my complaints so my question is: What persuasive counter-arguments can you come up with against this hacky design approach?

  • 1
    You've pretty much summed up what's wrong with this approach. – System Down Aug 27 '13 at 16:57
  • 6
    Is this a relational database? How could you possibly perform a join with this approach? – Jonathan Henson Aug 27 '13 at 17:00
  • We are using mysql. Yeah this is my other problem. It is impossible to query the database in a sensible way. – Adam Arold Aug 27 '13 at 17:10
  • Just say there needs to be a foreign key relationship between some_table and images. If they're worried about legacy code reading this table you can create the current some_table abomination through a view. – dcaswell Aug 27 '13 at 17:16
  • @user814064: I think transitioning them to properly designed tables and giving them views that still preserve the legacy structure could be a good idea, provided they won't try to isnert/update the views (does MySQL allow that?) – FrustratedWithFormsDesigner Aug 27 '13 at 18:05
2

It appears you have your complaints in order. I would come up with more, but I don't think you've identified why they chose this solution.

There are many benefits to normalizing and denormalizing a database. Look into how they feel this structure benefits the application. Hopefully they're trying to do more to sell you on these methods instead of just making you do it.

There could be a lack of relational database knowledge. They've already gone to the trouble of handling the relationships and referencial integrity in the application code. If this is causing a lot of bugs because they coded things that a RDBDMS handles almost out of the box, it makes sense to change the code instead of trying to maintain what you have.

Maybe there is a new module to the application where you have an opportunity to do things differently and show them the benefits? It's a hard sell on code that supposedly works.

Everyone is aware of best practices, technical debt, ease of debugging and enhancing, but these can be a hard-sell in situations where they may not apply (e.g. a legacy code system with a legacy team who are adept at their inefficient methods enough to convice those in charge they are getting things done on time and can't get any faster.).

| improve this answer | |
  • Everyone is aware of best practices ... not everyone, apparently. – Kirk Broadhurst Aug 27 '13 at 19:06
1

Some of the arguments I can think of against this design:

  • It's probably impossible to index on the references IDs. Sometimes you want to index a table not only by it's primary key, but also by what it references. I don't see an easy way to do that here.

  • Queries that join on these fields are going to get ugly. I'm guessing there's a lot of usage for in and string manipulation functions (like instr , substr, etc...).

  • Updating these lists of IDs is going to be ugly, since you have to update records and remove/add strings instead of simply inserting/deleting records, if this had been done properly with auxiliary tables storing these relations.

  • As you mentioned, this approach can also lead to data integrity problems.

  • All of the above could lead to performance problems.

  • It's counter-intuitive and will make be more difficult when new members join the team.

I don't know which of these you've already tried on them. The best approach might be to do a demo of a small set of their tables (maybe some_table and images): rebuild the tables with proper foreign keys and relation tables and show the performance gains, the ease-of-querying, they way you can't introduce invalid data... And I hope you are able to show some significant gains. If this design pattern is already so entrenched, it might be very hard to break them of it without a good demonstration.

| improve this answer | |

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.