4

I have a PostgreSQL 9.3.7 database scheme representing a directed graph of vertices and edges with some many-to-many relations. I'm only ever interested in getting all vertices that a given vertex points to or getting all vertices that point to a given vertex.

For each many-to-many relationship, I could simply give each of the two tables a bigint[] (array) column to represent what the object points to or is pointed from. When adding an object, I would go through all its bigint[] columns I'm inserting and append itself to the corresponding bigint[] columns (inverse relations) in other tables. Edit: Actually, I have some other many-to-many relations that need to be flexible, so I use separate tables for those. But I am guarenteed that for the ones in question, I only need the queries described above.

This seems more efficient than the conventional approach of making a separate table for the relationship, plus it's less complicated. But I've never heard of anyone doing this, so I'm worried. Would such an approach have any flaws that I'm not considering?

Improve this question
8
  • 1
    While it may work, I do not think it would be very malleable.. Good luck trying to do any sort of reporting on it? The whole point of normalization (the conventional approach) is that the resulting data structure is very malleable and can be used in many different ways. I'm pretty sure your idea will work for what you want it to do ... but that's probably ALL it will ever work for.
    – Joishi Bodio
    Commented Jun 23, 2015 at 18:25
  • Yes, I'm given the guarantee that I'll never need to make any other kinds of queries. If I need to do other queries, I see that this is very inflexible.
    – sudo
    Commented Jun 23, 2015 at 18:29
  • 2
    If it's a manager that's giving you that guarantee, I wouldn't trust it. :)
    – Joishi Bodio
    Commented Jun 23, 2015 at 18:42
  • 1
    I would hazard that the best approach from both an analytics and long-term data usage approach would be to keep the data stored in a normalized form, but materialize a view which contained the array structure you're considering.
    – Chris
    Commented Jun 23, 2015 at 19:25
  • "This seems more efficient than the conventional approach of making a separate table for the relationship". Why does it seem so? Have you done any testing/benchmarking to evaluate that? It could be for some query patterns, but I wouldn't make assumptions.... I'd do some testing and modelling.
    – Craig Ringer
    Commented Jun 24, 2015 at 0:56

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.