Entity Relationship Diagram for Roots Magic v7.5+ #datamodel #ERD #diagram

#1 · 2019-09-21, 7:29 pm

Hi Everyone,

The cliche: "A picture is worth a thousand words" rings true, especially with the this data model.

Attached, please find my first iteration of an ERD for Roots Magic 7.5+. Feel free to comment and make corrections.

Kind regards,

Bill

Uploaded files:

Mike66, Keith Botley and kevync have reacted to this post.

#2 · 2019-11-14, 2:47 am

Looking good. One extra link I've found:
MediaLinkTable - OwnerType 14 = Place Detail (PlaceType in PlaceTable = 2)

So OwnerType 5 is Place whilst OwnerType 14 is PlaceDetail

Only found by adding media to a Place Detail!

#3 · 2020-02-09, 12:07 am

This is a gold mine for me with what I want to do with this data, just to be clear these are not actual foreign keys in the diagram as they are not explicitly defined in the database? In other words they are references but not actual constraints.

Thanks

#4 · 2021-03-04, 12:06 pm

@billgirimonti

Bill, are you in a position to update your ERD for RM8?

Tom

kevync has reacted to this post.

#5 · 2021-03-21, 3:07 am

@billgirimonti there are no hard coded foreign keys in the database that I've found. I've done a Visio database diagram of RM8 now with as many of the relationships as I know. https://sqlitetoolsforrootsmagic.com/understanding-the-roots-magic-8-database-database-diagram/

#6 · 2023-03-04, 5:16 pm

@billgirimonti, @patjones

What do you use for generating ERD's from/for sqlite databases?

I looked around for an ERD editor (free) to try my hand at doing one for RM9 and came up with DBeaver which is a database manager. Started off great simply by opening a .rmtree file which populated the ERD editor with all the tables but no relationships. It even continued pretty well using auto-arrange after adding some linkages until at some point it pulled everything over into 4 tall columns. I manually relocated the tables into a squarer format but some lines went down to the bottom of the 'canvas' and many had quirky jumps in them that I couldn't eliminate. Tried auto-arrange again and everything snapped back to the 4 columns. So none too happy with DBeaver's ERD editor although it does have a nice feature that clicking on, say, PersonID, highlights all the links I've added for it.

Uploaded files:

#7 · 2023-03-04, 10:27 pm

Hi Tom,

I did it by hand with an image editing application, which, as you can imagine, was not exactly easy to do.

#8 · 2023-03-05, 3:12 am

Visio reverse engineers databases very easily. I use it all the time for work and so it is easy to port an RM database to my work pc to draw. I did RM8 and posted it on this site https://sqlitetoolsforrootsmagic.com/understanding-the-roots-magic-8-database-database-diagram/ and will be doing RM9 this week since it only takes me a few minutes.