2

I currently am using a graph database and am considering moving off of it to a relational database management system like PostgreSQL.

My concern is that I have lots of relationships between entities (since this is what a graph database is structured as) and I'm not sure the best way to handle these when moving to a relational database.

For example, I have tasks {id, projectid, scheduleid, name, desc, ...} that have a 1:m relationship with:

  • activities {id, name, startat, data, ...}
  • other tasks
  • materials {id, name, desc...}
  • invoice line items {id, price, qty, ...}
  • alerts {id, type...}
  • files {id, name, url}
  • messages {id, name, createdby, ...}
  • contacts {id, name, email...}
  • tags {id, name, ...}
  • equipment {id, name, ...}

Using the graph database I can start at one or more task nodes and then query all of the directly connected nodes which allows me to grab all of the associated data.

What's the strategy for modeling such highly connected data in a relational database without having to do 15 JOINs to get everything? I need all the data in order to populate my views and I really don't want to do one query to get the tasks and then 15 more queries to get the rest of the data.

Considerations

I'm considering creating a task_relationship table with {task_id, target_id, target_type, target_name, target_desc} which would contain links to anything that can be related to a task along with some basic denormalized data.

However, I would still need to do JOINs to get additional data for some things and other 1:1 relationships that would be stored with the task such as project information and schedule information.

I also wouldn't be able to use cascading updates when data changes so I would have to handle that via triggers or in code I think.

Questions

Is this a sane approach or is there a better way?

Also, if I have a server with 24 cores and 128GB of memory backed by a 8 disk SSD array - how many JOINs of million row tables can I do before queries start taking more than 50ms? I know that's impossible to answer but is it more:

  • “with only a few million rows you don't really need to worry about that”, or
  • “with a few million rows doing more than three or four JOINs will start to slow things down without very careful schema planning and optimizations”?
  • You had it at "15 joins to connect everything" or "15 more queries to get the rest of the data". It's an awkward aspect of RDBMSes, and something I'd really like to see improve with language extensions to allow FK traversals and structured nested resultsets. – Craig Ringer Apr 10 '15 at 1:42
0

Starting at the end - if you have, say, 20 large tables of 10 millions rows each at 200 bytes per row that works out at just under 40GB. Add twice as much for indexes and your entire database will fit into memory. With sensibile indexes in place you really shouldn't worry too much about joining multiple tables. Joins are what relational databases do, it's what their code is optimised for. Don't be frightend of using your new software for its intended purpose.

Have you considered having different views for each specific use-case? These will be easier to write and maintain. The optimiser will likely do a better job, too, if it has fewer objects to consider in each view.

If you want an alternative, however, you could try modeling your data in a star schema, like a data warehouse. Task will take on the "fact" role and the others taking on the "dimension" roles. You'll still have multiple joins but the opportunity for successive calculations on statistics producing silly answers is much reduced.

I would not try to shoehorn all the task-related values into one generic mega-table. Think what the SQL would look like if you implemented task_relationship. Does that really look better than joins on normalised tables?

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.