Torkil Johnsen

My personal piece of cyberspace

Joomla 1.5 database schema

Posted in Content management,Search engines on Apr 30th, 2006

NEW: Looking for the Joomla 1.6 database schema?

It’s right here: Joomla 1.6 database schema.

Continue to read about the 1.5 schema

Joomla 1.5 database schema, bigJoomla 1.5 beta is just around the corner now. Jinx has earlier stated that it would be released before may but we are yet to see if the core devs manage to keep their promise. As part of my involvement in the standards & guidelines group in Joomla, I set up a Joomla database schema to give 3rd party developers a clearer understanding as to how the core database is structured. Click the thumbnail to the right to see the full sized schema.

Download Joomla 1.5 database diagram (From April 2006. XML-format, for use with FabFORCE DBDesigner4)

The diagram also has column info expanded, so that you can see the column types (varchar, integer, etc). This makes the schema a bit messy, but this is a feature you easily can turn on/off in DBDesigner to make it more readable again.

[tags]database,fabforce,dbdesigner,mysql,joomla,schema[/tags]

  • http://ezpeezee.byethost6.com Olumide Otuyelu

    The schema layout you created is going to save a lot of people lots of time in understanding, utilizing, modifying, and most importantly enjoying working with the Joomla system.

    Itr sure gave me a better understanding of the relationship within the layout of the system. Reminds me why Visio digrams were invaluable in my days as a security consultant.

    Olumide Otuyelu

  • http://ezpeezee.byethost6.com Olumide Otuyelu

    The schema layout you created is going to save a lot of people lots of time in understanding, utilizing, modifying, and most importantly enjoying working with the Joomla system.

    Itr sure gave me a better understanding of the relationship within the layout of the system. Reminds me why Visio digrams were invaluable in my days as a security consultant.

    Olumide Otuyelu

  • http://www.torkiljohnsen.com tj

    Thanks!

    If you’re into database modelling I recommend checking out http://www.fabforce.net and their DBDesigner Open Source software, which I used to create this schema.

  • http://www.torkiljohnsen.com tj

    Thanks!

    If you’re into database modelling I recommend checking out http://www.fabforce.net and their DBDesigner Open Source software, which I used to create this schema.

  • http://community.nebraska.edu/amyblog/ Amy Stephen

    So nice…so VERY nice…Thanks for your contribution, Torkil, you database stud…well, except for your position on “a” and “b” aliases, but given more experience, you’ll get there! ;-)

  • http://community.nebraska.edu/amyblog/ Amy Stephen

    So nice…so VERY nice…Thanks for your contribution, Torkil, you database stud…well, except for your position on “a” and “b” aliases, but given more experience, you’ll get there! ;-)

  • http://www.tzo.com Scott Prive

    Hi TJ,

    It would be excellent if you could extend this posting with a printable PDF version of the schema.

    It would be also helpful to have the project file since you have already done the work of grouping tables, linking them and adding colored backgrounds. That way we can open it up and print from the pristine source.

    The large pixel image looks good on one sheet of paper, but less so if ’tiled’ over say 4 sheets of paper :-)

    I followed your link to the FabForce GPL software, and note here that the Linux version does not install on any modern Linux (the dependencies do not map to anything supported on Fedora Core 6… even my careful install of old RH9 rpms did not help). The irony here is the Windows version works perfectly under WINE so far (except that your project file is now needed ).

  • http://www.tzo.com Scott Prive

    Hi TJ,

    It would be excellent if you could extend this posting with a printable PDF version of the schema.

    It would be also helpful to have the project file since you have already done the work of grouping tables, linking them and adding colored backgrounds. That way we can open it up and print from the pristine source.

    The large pixel image looks good on one sheet of paper, but less so if ’tiled’ over say 4 sheets of paper :-)

    I followed your link to the FabForce GPL software, and note here that the Linux version does not install on any modern Linux (the dependencies do not map to anything supported on Fedora Core 6… even my careful install of old RH9 rpms did not help). The irony here is the Windows version works perfectly under WINE so far (except that your project file is now needed ).

  • http://www.torkiljohnsen.com tj

    Hi Scott!

    A pdf would have been extremely handy, except that DBdesigner does not export to pdf. Another suggestion was made to also display the data types in the diagram, which I agree upon, so I have planned a new version, but not finished it yet.

    Another problem with data types and relations is that the relations in DBdesigner assume that key->foreign key relations always have the same datatypes, something that isn’t always the case in the Joomla database, at least the last time I checked.

    Do you have suggestions for maybe an open source modelling tool that could be used that would also allow me to export to pdf?

  • http://www.torkiljohnsen.com tj

    Hi Scott!

    A pdf would have been extremely handy, except that DBdesigner does not export to pdf. Another suggestion was made to also display the data types in the diagram, which I agree upon, so I have planned a new version, but not finished it yet.

    Another problem with data types and relations is that the relations in DBdesigner assume that key->foreign key relations always have the same datatypes, something that isn’t always the case in the Joomla database, at least the last time I checked.

    Do you have suggestions for maybe an open source modelling tool that could be used that would also allow me to export to pdf?

  • Pingback: Database Schema di Joomla 1.5 · Copes Flavio Weblog

  • Scott Prive

    Hi TJ,

    I believe this will get you a PDF from most any software:

    Print to Postscript File (you can do this in Windows… I assume you use Windows as Kylix is abandoned by Borland and requires really old distros).

    In Windows, there’s a number of .PS –> .PDF utilities (pick one), or use ‘ps2pdf’ in Linux.

    Generally Postscript is resolution independent so paper size SHOULDN’T matter… but if it looks all bitmappy then they’re exporting the graph as a bitmap (ugh), and oversized paper might compensate.

    You can email me with a zip of the PS if you need someone to run or test the output of ps2pdf.

  • Scott Prive

    Hi TJ,

    I believe this will get you a PDF from most any software:

    Print to Postscript File (you can do this in Windows… I assume you use Windows as Kylix is abandoned by Borland and requires really old distros).

    In Windows, there’s a number of .PS –> .PDF utilities (pick one), or use ‘ps2pdf’ in Linux.

    Generally Postscript is resolution independent so paper size SHOULDN’T matter… but if it looks all bitmappy then they’re exporting the graph as a bitmap (ugh), and oversized paper might compensate.

    You can email me with a zip of the PS if you need someone to run or test the output of ps2pdf.

  • http://llajta.com fernando

    Is there anyway that you can upload the XML file from Fabforce? I have it on my PC and I like how you can setup the print options for the diagram to print in multiple pages. I can’t really do that with an image.

    Thanks for the hard work, this will help alot.

  • http://llajta.com fernando

    Is there anyway that you can upload the XML file from Fabforce? I have it on my PC and I like how you can setup the print options for the diagram to print in multiple pages. I can’t really do that with an image.

    Thanks for the hard work, this will help alot.

  • http://www.torkiljohnsen.com tj

    Hi fernando!

    Not a problem! I have uploaded the schema now, you’ll find a link to it in the post above. Just download the zip and decompress it, and you should get an xml-file ready to be imported into DBDesigner!

    Since I have done the groundwork in April last year, the database structure has probably changed a bit. I’d really appreciate it if you would share with me an updated model should you decide to make one.

    Please note that the checked_out field is in fact a foreign key to the users.id field in many tables, but has been left out of the diagram to keep it readable. Please see my note on the right side of the diagram.

    - Torkil

  • http://www.torkiljohnsen.com tj

    Hi fernando!

    Not a problem! I have uploaded the schema now, you’ll find a link to it in the post above. Just download the zip and decompress it, and you should get an xml-file ready to be imported into DBDesigner!

    Since I have done the groundwork in April last year, the database structure has probably changed a bit. I’d really appreciate it if you would share with me an updated model should you decide to make one.

    Please note that the checked_out field is in fact a foreign key to the users.id field in many tables, but has been left out of the diagram to keep it readable. Please see my note on the right side of the diagram.

    - Torkil

  • http://www.llajta.com fernando

    Thanks Torkil!

    I’m just getting into the whole DB Model and creating my first component, it will be a while before I have a good understanding of the structure and dependencies between all the objects. If I start modifying things I certainly keep you posted.

    FYI: When I imported the XML, the diagram didn’t look like the PNG you have. All the tables for the different colored sections were lumped together, Some of the large tables were ‘overflowing’ from their respective colored areas as well.

    Nevertheless this is fantastic and it prints great on 4 Tabloid pages.
    Thanks once more

    Fernando

  • http://www.llajta.com fernando

    Thanks Torkil!

    I’m just getting into the whole DB Model and creating my first component, it will be a while before I have a good understanding of the structure and dependencies between all the objects. If I start modifying things I certainly keep you posted.

    FYI: When I imported the XML, the diagram didn’t look like the PNG you have. All the tables for the different colored sections were lumped together, Some of the large tables were ‘overflowing’ from their respective colored areas as well.

    Nevertheless this is fantastic and it prints great on 4 Tabloid pages.
    Thanks once more

    Fernando

  • http://www.torkiljohnsen.com tj

    Yes I know, the diagram does not look like the png, and the tables are a bit lumped together because they display the different column types (varchar, integer, etc) as well as the column names.

    Somewhere inside DBDesigner — can’t remember where :) — you can switch on/off this feature to make the diagram more readable.

    Hope this helps, and remember the diagram is old. The database has changed since I made it, so an update is required! *hint hint, nudge nudge*

  • http://www.torkiljohnsen.com tj

    Yes I know, the diagram does not look like the png, and the tables are a bit lumped together because they display the different column types (varchar, integer, etc) as well as the column names.

    Somewhere inside DBDesigner — can’t remember where :) — you can switch on/off this feature to make the diagram more readable.

    Hope this helps, and remember the diagram is old. The database has changed since I made it, so an update is required! *hint hint, nudge nudge*

  • http://www.torkiljohnsen.com tj

    I edited the original post above to clarify this point :) Thanks for the heads up!

  • http://www.torkiljohnsen.com tj

    I edited the original post above to clarify this point :) Thanks for the heads up!

  • http://www.llajta.com grtfercho

    Thanks again Torkil.

    I looked around on DBDesigner and found out how to make the diagram exactly like the PNG.
    I’m posting back here for reference for other visitors.
    ————————————————–
    I’m assuming that you, just like me are hearing about DBDesigner for the first time thanks to this page. I have DBDesigner 4.
    Install DBDesigner, download the XML provided by Torkil and then go to File>Open and find the XML you downloaded.

    In DBDesigner you can go to the top menu and select ‘Display’. This option controls what you see on the diagram. The three sections that you are interested in are the Notation, Table Columns and Table Indices. Also notice the option ‘Display Relation Names’.

    1. To show/hide the data type go to ;Display>Table Columns>Physical Schema Level.
    2. To show/hide the Indices go to; Display>Table Indices>List Table Indices
    3. To Show/hide Foreign Keys go to; Display>Table Columns> Show Foreign Keys.
    4. To hide, I see no reason to show, the Relationship Names go to ;Display>[Uncheck] Display Relation Names

    5. To change the Notation go to Display>Notation and choose your option of EER, EER[1..n] Shows numeric relation, Traditional and Crows Foot.

    Pretty basic stuff but if you are like me and want to get things done quickly without reading the whole manual, this should help.

    ————————

    Torkil, If I start getting more involved with the DBDesigner and Joomla I may start doing some updates to the diagram.
    I will definitely let you know and send the file your way if I change/add things.

    Thanks again

    Fernando

  • http://www.llajta.com grtfercho

    Thanks again Torkil.

    I looked around on DBDesigner and found out how to make the diagram exactly like the PNG.
    I’m posting back here for reference for other visitors.
    ————————————————–
    I’m assuming that you, just like me are hearing about DBDesigner for the first time thanks to this page. I have DBDesigner 4.
    Install DBDesigner, download the XML provided by Torkil and then go to File>Open and find the XML you downloaded.

    In DBDesigner you can go to the top menu and select ‘Display’. This option controls what you see on the diagram. The three sections that you are interested in are the Notation, Table Columns and Table Indices. Also notice the option ‘Display Relation Names’.

    1. To show/hide the data type go to ;Display>Table Columns>Physical Schema Level.
    2. To show/hide the Indices go to; Display>Table Indices>List Table Indices
    3. To Show/hide Foreign Keys go to; Display>Table Columns> Show Foreign Keys.
    4. To hide, I see no reason to show, the Relationship Names go to ;Display>[Uncheck] Display Relation Names

    5. To change the Notation go to Display>Notation and choose your option of EER, EER[1..n] Shows numeric relation, Traditional and Crows Foot.

    Pretty basic stuff but if you are like me and want to get things done quickly without reading the whole manual, this should help.

    ————————

    Torkil, If I start getting more involved with the DBDesigner and Joomla I may start doing some updates to the diagram.
    I will definitely let you know and send the file your way if I change/add things.

    Thanks again

    Fernando

  • http://www.torkiljohnsen.com tj

    Sounds great Fernando, and thanks for the reference.

  • http://www.torkiljohnsen.com tj

    Sounds great Fernando, and thanks for the reference.

  • http://www.ego2to.cn chuchunliang

    Dear web master,
    Why 3 table have link each other. eg.jos_contetn,jos_categories and jos_sections.I do not understand why designed like that,please,give one of your hand to help me.
    think you.best wishes.

  • http://www.ego2to.cn chuchunliang

    Dear web master,
    Why 3 table have link each other. eg.jos_contetn,jos_categories and jos_sections.I do not understand why designed like that,please,give one of your hand to help me.
    think you.best wishes.

  • http://www.ciozone.com JLee

    Well done! You rock brother. I’m very surprised the official Joomla docs don’t have this.

  • http://www.ciozone.com JLee

    Well done! You rock brother. I’m very surprised the official Joomla docs don’t have this.

  • Pingback: Torkil Johnsen » Joomla 1.6 database schema

  • Pingback: Torkil Johnsen » The Joomla database schema smells

  • Pingback: Joomla 1.5 database schema/data model « Blue Crystal Web Design's Blog

  • http://www.torkiljohnsen.com tj

    New: Joomla 1.6 database schema is now out:
    http://www.torkiljohnsen.com/2010/07/11/joomla-1-6-database-schema/

  • http://www.torkiljohnsen.com tj

    New: Joomla 1.6 database schema is now out:
    http://www.torkiljohnsen.com/2010/07/11/joomla-1-6-database-schema/

  • Wai

    Thanks a lot. But why are there only 34 tables? Actually, joomla 1.5 has 36 tables. Is it because of joomla1.5 beta database schema? Where can I get the schema for joomla 1.5 stable?


Creative Commons License
This work by Torkil Johnsen is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.