Orchard Database Diagram and Referential Integrity?

Topics: Administration, General, Writing modules
Aug 26, 2011 at 8:10 PM

Has anyone published a ER diagram for the Orchard database?

I was going to use the SQL Server Management Studio diagram tools to create one, but since there are no relationships declared in the database, the diagram tool is pretty much useless.

Has anyone created a SQL script to add the key relationships to the database? or is this old-school thinking and unnecessary in the world of NHibernate and ORM?

Aug 27, 2011 at 4:04 AM

I don't think it has as much to do with NHibernate as it does with the fact that extensibility is a first class feature in Orchard.  Traditional applications are not build with this much flexibility in mind, so in most cases I would expect to see FK constraints, even when using NHibernate or any other ORM.  

With Orchard the Modules that are installed drive the persistence mechanism, so adding Foreign Keys could be seen as going against the grain a bit because we are relying on the application to take care of it.

Sep 7, 2011 at 4:33 PM

I assume there is a relationship between tables like Navigation_MenuPartRecord and Navigation_MenuItemPartRecord. Putting in FK constraints on some of these core tables wouldn't limit module extensibility. Also, documenting them in a database diagram would be useful.

Sep 8, 2011 at 10:42 PM

I was also a little confused on the relationships and how things tie together, I understand the database needs to be flexible and a column might reference columns in different tables for example.

However it would be good to have this at least documented so we could understand when looking at the vast amount of tables to try and get a grasp on how it all works under the hood.

Oct 27, 2011 at 12:52 AM

Orchard Team: Is there a diagram and/or any documentation that describes the relationships between the various Orchard DB entities? Trying to figure out how the entities relate to one another and how to reconstruct logical entities from the physical DB is oftentimes very confusing.

Oct 27, 2011 at 2:18 AM

No, there is no such diagram. Why would you want to reconstruct logical entities from the physical DB?

Oct 27, 2011 at 4:24 PM

Because I might want to write a tool to help automate the bulk updating of a number of pages within a site. Trying to figure out how entities relate to one another isn't always clear ... as I state here: http://orchard.codeplex.com/discussions/277353

Oct 28, 2011 at 12:05 AM

And why don't you do that through the content manager?

Oct 31, 2011 at 11:06 PM

I need to extract MANY pages from an existing site's MySQL DB and then pull each page, do some processing on the page content and push the newly scrubbed content up into SQLAzure. Want to do this from a console/WinForms app to give the operator some control over the process.

Oct 31, 2011 at 11:13 PM

how about doing it in small batches through export and the command-line?

Nov 1, 2011 at 12:10 AM

Let me take a look at the import command line. That MAY give me enough control, otherwise, I'd like to decypher the DB schema. Is there any documentation that describes how the database works?

I can also foresee a requirement to purge the DB of old content that is no longer reachable - does the Orchard OM provide for such a capability today?

Nov 1, 2011 at 10:23 AM

There's no documentation I'm aware of, since the schema is created for you by the modular framework and migrations system. But, it's not hard to work out; create some content items of different types and have a look at what records show up.