Database Diagram

Replies to This Discussion

Permalink Reply by David Vega on October 25, 2009 at 8:45pm

Hi, good idea, I'd like to suggest a tool I have found a great instrument for both design and reverse engineering, it is called MySQL Workbench and all one needs is to feed it the script to generate the diagrams, and if needed manipulate them and then forward engineer the SQL again.

After that, the diffing should be fairly straight forward

Permalink Reply by Amy Stephen on October 27, 2009 at 7:48pm

How we doing?

Permalink Reply by David Vega on October 28, 2009 at 12:37pm

I am currently working on the 1.6 diagram in MySQL Workbench, shuffling and placing tables and layers. Shall we use this as a temporal workspace?

Permalink Reply by Amy Stephen on October 28, 2009 at 1:09pm

Yes, please! Would love to learn from your work - and it will give people a chance to work you. It'll be nice to have a 1.6 diagram, too. Very helpful!

Permalink Reply by David Vega on October 28, 2009 at 1:57pm

Ok, here are both =)

I'm attaching the MySQL Workbench (http://wb.mysql.com/) files, the SQL scripts (with the #__ prefix removed) and a PNG for each diagram (1.5 and 1.6)

Attachments:

• j16migration_diagrams.rar, 766 KB

Permalink Reply by Amy Stephen on October 28, 2009 at 2:13pm

David - I can't download that file - it says "Access Denied"

Permalink Reply by Marco Barbosa on October 28, 2009 at 3:11pm

Me neither! Perhaps you can upload to mediafire or something ?

Permalink Reply by David Vega on October 28, 2009 at 10:18pm

Oh, sorry. Well, not even I have access, ha.

I reuploaded the file to rapidshare: http://rapidshare.com/files/299365808/j16migration_diagrams.rar

Permalink Reply by David Vega on October 29, 2009 at 10:06pm

BTW, I was thinking about putting foreign keys since the engine is MyISAM and it wouldn't affect (as far as I know since MyISAM lacks referential integrity) the behavior of Joomla and it would help Doctrine discover and generate the relationships.

Only thing is that I didn't find all fks to be obvious.

Also, something bothers my OCD and it is that I couldn't find any database conventions here: http://docs.joomla.org/Coding_style_and_standards

My OCD thinks this could be improved (that nosy bastard):

- Not all PKs are called id even though most are, e.g. messages.message_id, banner.bid, bannerclient.cid
- Not all tables have the PK in the first place, e.g. newsfeeds
- Some "FKs" are abbreviated, some are not, e.g. access_rules.section_id is nice, while banner.cid is not
- I "think" not all data types are optimized (though most are), e.g. components.enabled is TINYINT(4)
- There is a sections table?
- And last but not least, no db level referential integrity (even though MyISAM just supports the notation and not the behaviors that could be nice enough... and perhaps future support for InnoDB?, it has side benefits! [such as nulls actually not using storage space])

I know all this last thing is not the objective but didn't want to overlook it, I mean, the OCD =)

Permalink Reply by Amy Stephen on October 30, 2009 at 12:38am

You might want to post this, on list, and ask Andrew what he thinks. Those looks like good cleanup questions - and good to ask before the migration. He is aware of this thread - but asking on list is best. Not certain how far they'll want to go with that, given the coding implications. I have database OCD, too. :-P

Thanks for your work!

Permalink Reply by David Vega on October 30, 2009 at 8:01am

Done, and yw =)

Permalink Reply by David Vega on October 31, 2009 at 5:16pm

Andrew has started a new discussion on the schema matter over here.

There are some ideas about making an upgrader component that would participate only in the installation process (if I understood correctly)

• ‹ Previous
• 1
• 2
• Next ›
• Page