Self reference using a new table vs concatenated list of ids

Question

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?

Answer 1

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.).

Answer 2

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.