Joomla!
http://forum.joomla.org/

1.5 DB schema
http://forum.joomla.org/viewtopic.php?f=304&t=55622		 Page 1 of 4
Author:  torkil [ Tue Apr 18, 2006 12:43 pm ]
Post subject:  1.5 DB schema
I am working on setting up a graphical presentation of the J!1.5 DB schema, in classical crow foot notation :)

While doing this I have examined most of these tables, and I have noticed alot of unused or inactive fields and a few tables too. Would reporting these be of any interest to the devs, or is the DB schema frozen for 1.5?
Author:  Jinx [ Tue Apr 18, 2006 12:59 pm ]
Post subject:  Re: 1.5 DB schema
These would be of interest indeed. :)
Author:  torkil [ Tue Apr 18, 2006 1:24 pm ]
Post subject:  Re: 1.5 DB schema [rev 3148]
Okay. I do lack some knowledge of the inner workings, so many of my guesses are due to the fact that I do not know what role the fields play, should play, or are planned to play. Here are the fields:

  • jos_banner.editor: I don't see what this field does.
  • jos_banner.type: The default banners have all type set to "banner". I don't see any place where I can add a "type" in the add form. When I add a new banner, the new banner gets type set to '' (nothing).
  • jos_bannerclient.editor: I don't see what this field does.
  • jos_bannerfinished: What does this table do? Seems like it's a new version of jos_banner that hasn't been completed. Seems like this version has support for startdate and stopdate, and not just impressions/clicks.
  • jos_categories.parent_id: Not in use by the core, but maybe by 3rd party components?
  • jos_categories.editor: What does this do?
  • jos_components.menuid: What does this do? Same as jos_menu.componentid, which also does nothing?
  • jos_content.parentid: Not in use?
  • jos_core_acl-tables: I guess these will get a workover when the new ACL-stuff is in place?
  • jos_menu.pollid: Hm. Not working? Supposed to point to a poll ID? I don't see that this is possible to set up right now.
  • jos_menu.browserNav: No clue what this does!
  • jos_messages.folder_id: Does the messaging system have folders? Might be keeping this for later on?
  • jos_messages.priority: Does the messaging system have message priority implemented?
  • jos_modules.client_id: I can see that this field is in use somehow, but what does it do?
  • jos_newsfeeds.filename: In my default installation, this field isn't used at all. Seems like the link field serves this purpose?
  • jos_newsfeeds: The order of the catid and id columns should be changed. Id should be on top. I know I'm weird...
  • jos_plugins.client_id: Another cilent_id field. What are these for?
  • jos_sections.scope: Hard to say if this is used beyond the value "content".
  • jos_session.client_id: Another client_id field... Just wondering what good they do!
  • jos_session.guest: The session table already has username and user_id in it, is there a need for a guest field too?
  • jos_weblinks.sid: Doesn't seem to be in use.
Author:  torkil [ Tue Apr 18, 2006 1:55 pm ]
Post subject:  Re: 1.5 DB schema
Here is a link to the graphical version that I'm working on. I am guessing we can call this a temporary version still :)

Joomla! 1.5 - CMS database schema

Edit: removed the Norwegian'ish url...
Author:  AmyStephen [ Tue Apr 18, 2006 1:56 pm ]
Post subject:  Re: 1.5 DB schema
Torkil, from the bottom of my DBA heart, I thank you! Keep doing great things!
Amy

:) WOW!   :)
Author:  Scribe [ Tue Apr 18, 2006 6:07 pm ]
Post subject:  Re: 1.5 DB schema
Great stuff Torkil,

I've just started looking at the schema and this will really help. How do you determine the foreign key relationships with out any foreign key declarations in the sql file  ???
Author:  Tonie [ Tue Apr 18, 2006 6:11 pm ]
Post subject:  Re: 1.5 DB schema
Cool, how have you created this? Did you have an application to do this?
Author:  AmyStephen [ Tue Apr 18, 2006 6:22 pm ]
Post subject:  Re: 1.5 DB schema
Scribe wrote:
How do you determine the foreign key relationships with out any foreign key declarations in the sql file  ???


He probably used like named data elements with matching data types and some knowledge of the tables -- how the system screws together. But, even if it is not technically implemented as a primary and foreign key in the DB, I am pleased to see it documented in that fashion.

Just this morning, someone asked about pumping in an external list of users. It is even *more* important to understand the referential integrity requirements if the DB is absent these constraints.

I am curious, like Tonie, Torkil. What did you use to document this? An open source data modeling tool? Also, are you able to depict the data model showing the data type in the table? ALSO: what does the model look like if you document the key field names on your table joins? Too busy? IMO these types of change would be even better, yet.

Thanks, again. VERY nice.
Author:  torkil [ Tue Apr 18, 2006 6:49 pm ]
Post subject:  Re: 1.5 DB schema
I'll try answering some questions then!

Software: I used the excellent Open Source DBDesigner from FabForce.net. DBDesigner is available for Windows and Linux, but I've only tested it under Windows. It works smoothly once you understand how to do stuff. For instance: When you set up a foreign key relation the foreign key gets named automatically. It took me a couple of hours to find out that you have to doubleclick the relationship symbol (crow foot line) to edit this name.

What I did: Did a fresh Joomla install from the SVN. Set up a connection to my database and did reverse engeneering, meaning I imported the tables with datatypes, field names and primary keys set up. Then I set up all foreign key relations by hand... Took some time...

Foreign keys: Correct assumption by Amy: I used a bit of knowledge combined with a bit of guessing and a bit of testing by inserting data here and there in the admin system. The database fields should be named so that relations are self explanatory, and this is also the case for the joomla database - to a certain degree - so it wasn't too hard, it just took some time. Hence my list of remarks/questions above to the devs.

About showing data types in the schema: Yes, you can show data types too, but the problem was the connection between jos_sections.id and jos_categories.section. The thing is that jos_categories.section is of datatype varchar and jos_sections.id is of datatype int. The software assumes that key-foreignkey has the same datatype, so I then had two choices: 1) Display the schema with a wrong datatype in one spot or 2) Dont display datatypes at all. Maybe you can set up a foreign key that isnt of the same datatype as the key, I'm not sure, but it might be possible. I don't know how to do this in DBDesigner though.

Detailing relations: Yes it is possible to add labels to all relationships, but I have hidden these for the same reason I hid all the checked_out_by-relations: To make the schema readable. (read the note in the bottom right corner)
Author:  Scribe [ Tue Apr 18, 2006 7:08 pm ]
Post subject:  Re: 1.5 DB schema
I was also in the process of using DB Designer to document the schema but didn't get as far as you with the foreign key definitions.  There's an option in reverse engineering to have DB Designer generate the relationships for you but when you enable that it generates quite a rat's nest! That's when I started looking closer at the schema but many of the data elements don't seem to be named the same so I turned on debugging and started anylzing things like ON cc.id = a.catid or ON s.id = a.sectionid. I can skip all that now  ;D

So torkil, if the foreign key definitions were added to the schema, do you beleive that DB designer would be able to read the relationships from MyISAM tables and document the schema correctly? I know MyISAM tables will allow you to put the foreign key relationships in even though it won't do anything with them but I'm not sure if apps like DB Designer will be able to read them back properly.
Author:  torkil [ Tue Apr 18, 2006 7:13 pm ]
Post subject:  Re: 1.5 DB schema
DBDesigner is able to read all the primary keys and I think also all the unique definitions, so it should be able to handle foreign keys too.

DBDesigner can do reverse engeneering, modelling and synchronization, so you can implement the changes in the model into the database too. Really nice :)
Author:  Scribe [ Tue Apr 18, 2006 7:21 pm ]
Post subject:  Re: 1.5 DB schema
Yeah it seems pretty powerful but I'm still learning. I still want to experiment with foreign keys defined in the table create statements and then see if DB Designer can geneate the relationship diagram by itself.

If that works, does it seem like it would be worthwhile to add the foreign key definitions back into the table creates so this type of drawing could be generated automatically? I know that's kind of putting the cart before the horse but it might make it easier for 3PDs to actually have the foreign keys defined in the joomla.sql file.
Author:  torkil [ Tue Apr 18, 2006 7:21 pm ]
Post subject:  Re: 1.5 DB schema
On a sidenote: I think the fieldnames are a bit confusing at times. There seems to be no naming guidelines at all. In the queries in the php code as well... There should be better guidelines as to how you write them, so that they are easier to read. Your example with cc.id = a.catid is typical. What is cc? What is a?

A classic example:
Code:
select * from jos_mycomponent as a left join jos_categories as b ON a.catid = b.id


An easier read in my opinion:
Code:
SELECT *
FROM jos_mycomponent AS mycomp
LEFT JOIN jos_categories AS cats ON mycomp.catid = cats.id
Author:  torkil [ Tue Apr 18, 2006 7:24 pm ]
Post subject:  Re: 1.5 DB schema
Scribe wrote:
Yeah it seems pretty powerful but I'm still learning. I still want to experiment with foreign keys defined in the table create statements and then see if DB Designer can geneate the relationship diagram by itself.

If that works, does it seem like it would be worthwhile to add the foreign key definitions back into the table creates so this type of drawing could be generated automatically? I know that's kind of putting the cart before the horse but it might make it easier for 3PDs to actually have the foreign keys defined in the joomla.sql file.


I would have tested it right away, but I'm at home now with my Mac, so no can do :)

Foreign key definitions would be nice since it explains structure, much like comments in php code does. In MyISAM they might not make much of a difference, but for claritys sake it's definately nice to have.
Author:  Scribe [ Tue Apr 18, 2006 7:30 pm ]
Post subject:  Re: 1.5 DB schema
Agreed!

BTW, IMHO an even easier read would be

SELECT *
FROM jos_mycomponent AS mycomp
LEFT JOIN jos_categories AS cats ON mycomp.catid = cats.catid

I'm just used to having key names match.
Author:  AmyStephen [ Tue Apr 18, 2006 7:39 pm ]
Post subject:  Re: 1.5 DB schema
Nice. I do aliases all of the time with "a" and "b", too. Just helps the developer reduce key strokes in the joins and with the where clauses. I think it is commonly accepted. And I do *not* like outer joins, Torkil!

Maybe we are discovering DBA age difference issues? < you guys just need a little more experience. >

So, did you walk through all the code to help you document this? < Like coming up with the data elements you did not see used? > How long did that take you? And, am I understanding you to say that the foreign key constraints ARE in place in the mySQL db? (which is good, of course.)

OK - I'm going to download the tool. Thanks, again, Torkil, for everything. This is important work you are doing for the community.
Author:  torkil [ Tue Apr 18, 2006 7:53 pm ]
Post subject:  Re: 1.5 DB schema
@Scribe: I think cats.id is pretty self explanatory, and if you use my notation you can easily distinguish the FK from the PK: Anything named id is a primary key, anything named (something)_id is a foreign key. This is also why I like the underscore before id: It's easier to read cat_id and understand than catid. The eye catches the underscore in a way, if you know what I mean.

And: category_id is better than cat_id too. No room for misunderstandings! As things stand now, there are no clear naming convensions.

Ps: The schema also has some fields named lft and rgt. Bad naming if you ask me.

@Amy: Aliases are nice, but not at the cost of readability. Sorry for the outer join there. I just wanted to write a couple of UPPER CASE words.

I did not walk through the code, I used the administrator and looked at the data that was inserted. I might be wrong in many cases and the fields might be used or planned to be used. I spent a few hours last night and some hours after work today. Don't know how much time I put in exactly.

About the foreign keys, here are some quotes from mysql.com:
Quote:
In MySQL Server 3.23.44 and up, the InnoDB  storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See Section 14.2.6.4, “FOREIGN KEY Constraints” (...) At a later stage, foreign key constraints will be implemented for MyISAM tables as well.


Read more: http://dev.mysql.com/doc/refman/5.1/en/ ... -keys.html
Author:  AmyStephen [ Tue Apr 18, 2006 8:01 pm ]
Post subject:  Re: 1.5 DB schema
Also, getting the schema into a case tool that can generate DDL for various DBMS is going to be critical to supporting DB's other than mySQL. I can't seem to find DBDesigner's roadmap -- they have apparently shut down their forum due to "attacks". They say there are 3rd party plugins. Do you know if there are plugins for other DBMS?

Of course, with what you have done, Torkil, we can much more easily move into an ERwin or Designer.

++++++++++=

< you are, of course, right on the aliases. it is just a pretty universally accepted "sloppy habit" that many of us do! i also agree with 'category' rather than 'cat' - for some reason i don't mind extra key strokes there, go figure. "lft" and "rgt" -- hmmmm? me no like, either. >

You are going to need a PR guy, Torkil. Better give MediaMagnet a call! You probably have new found sympathy of why the Devs hate the forums! Do something, answer tons of silly questions. (and this repeats in an apparent endless loop.)

BTW: WHERE looks good in uppercase!
Author:  torkil [ Tue Apr 18, 2006 8:08 pm ]
Post subject:  Re: 1.5 DB schema
I haven't looked into any 3rd party plugins, sorry, Ive just used DBDesigner to plan my databases now and then.

@Amy: I love this discussion. This is my kind of stuff. Other discussions however (check my post log for lots of Norwegian nonsense) are not so rewarding... Especially when you're answering questions you have answered 10(000) times before or really easy questions from total newbies. O well... we were all newbies once. :)
Author:  Scribe [ Tue Apr 18, 2006 8:17 pm ]
Post subject:  Re: 1.5 DB schema
Amy , in Torkil's example he did use aliases, mycomp and cats; a and b were great for basic programmers when I was a kid (let's not discuss age) but then programmers learned that more descriptive variable names were easier to read, hence the whole self documenting thing. Perhaps this hasn't taken off in the DBA world yet  ;)

I think foreign key definitions in the schema would have drastically reduced the analysis time required. It would have been self documenting! MySQL does allow foreign key definitions in the create statements with MyISAM tables and has for many years. It doesn't do anything with them  but it certainly can clarify the design. I've used their true foreign key constraints under INNODB. It's nice this is finally available in MySQL.

BTW, I just created a simple two table system, and pointed DB Designer at it. It sucked it in and created a relationship for me when I did the reverse enginerring. I'm not sure if it deduced this off the matching names or the foreign key statement and I'm don't know how it would scale to a large schema; more playing needed.

Torkil, I'm not saying all PK and FK names need to always match but if there isn't a compelling reason not to, why wouldn't you?

Any yes Amy, I'm sure you have much more experience with DBs than I do but I doubt you're older!
Author:  AmyStephen [ Tue Apr 18, 2006 8:27 pm ]
Post subject:  Re: 1.5 DB schema
torkil wrote:
I love this discussion.


I am glad you feel that way. It is obviously fun for the rest of us, as well. The database is where it's at. Applications come and go but the data model seldom changes -- or at least not much.

One more thing, then, I need to do "real" work. Torkil, please add your name to the model. I am not suggesting this so that you are claiming your work (which you should do) but more because down the road, there might be questions and knowing who did the reverse engineering and doc will be helpful. You can make it clear you did not design the database but that you documented it and on what date; what version.

Programmer's "sign" their programs, User's Guide folks have their names listed, ... you get the idea.

OK - THANKS AGAIN! You are now a Joomla! superstar. Before, today, I don't know what you were.

++++

@Scribe - lol - I conceded the point on the aliases. He's right! < But, as long as you guys don't review *my* code at home, I will secretly continue with my very naughty habit. I don't smoke anymore. I drink very infrequently. But, dammit. I am using "a" and "b" when noone is looking. >

Reverse engineering tools (or ones that are any good) should document the FK constraint if it is in the DDL or the DB implementation.

I agree that the PK and FK should match - BUT Torkil is not designing a database, but documenting it. So, his design must reflect reality. And, I am pretty sure if Torkil approached Johan right now with a clean list of data elements (i.e., full words, underscores, like named columns) and suggested we run with it for v 1.5 --- well, I think that might be the last we'd see of Torkil!)

45 in September. Both my age and the number of years I have worked with databases, or so it seems!

--> Good discussion guys! THANKS!
Author:  torkil [ Tue Apr 18, 2006 9:24 pm ]
Post subject:  Re: 1.5 DB schema
Well... I prefer things like this:

Code:
jos_content
-----------------
id (PK)
category_id (FK)
title
introtext
content
blabla

jos_category
------------------
id (PK)
section_id (FK)
name
title


I prefer different names for the primary key jos_category.id and the foreign key jos_category.category_id because:
a) if you use the values in a query you immediately understand which is which. table1.id = primary key in table1, table2.table1_id = foreign key for id in table1. It just provides more info.
b) if you're looking at a db schema without FK definitions, its easy to notice the "_id" to identify the FKs.

And: If you do like Amy and use aliases like "a" and "b", then you will definately benefit from reading "a.id" and "b.category_id" as opposed to "a.category_id" and "b.category_id", which are much harder to tell apart.
Author:  AmyStephen [ Tue Apr 18, 2006 9:35 pm ]
Post subject:  Re: 1.5 DB schema
torkil wrote:
And: If you do like Amy and use aliases like "a" and "b", then you will definitely benefit from reading "a.id" and "b.category_id" as opposed to "a.category_id" and "b.category_id", which are much harder to tell apart.


First of all, allow me to point out that blabla should be blah_blah. Right?

But, truly, as soon as I posted, I knew you were going to pull that "more pure" DBA stuff on me about the different named keys elements. In the purest form with the most pure of database minds, there will be NO data elements named the same.

Torkil - I am a child of the 60's; those were not pure times.

If it is an employee_name (ok - scratch that...employee_first_name), then - that's how i want to see it everywhere. And, yes, on occasion, some amount of denormalization can be helpful. So, let's not EVEN start that discussion. Did you ever hear of reporting extracts? or data warehouses?

...and leave me alone already on the "a" and "b" aliases!!!! I'll bet you have skeletons in your closet, Mr. Torkil.

And -- hey -- your jos_content table name is not neatly lined up on the left in your example.

++++++++

We better stop. There was only ONE TIME that I have been escorted out of the forums...
Author:  Scribe [ Tue Apr 18, 2006 10:03 pm ]
Post subject:  Re: 1.5 DB schema
Amy, you're kind of fun when you're riled up  :D

50 in November, but I wasn't writing databases in the 60's  :o Wow!

As much as I try not to use a and b anymore I'll defed you're right to do so to the death.... well maybe not to the death!

But I still think it would be a good addition to 1.5 to back annotate the schema with Torkil's results, when he's done. We could save future generations from having this same discussion  :laugh:
Author:  AmyStephen [ Tue Apr 18, 2006 10:13 pm ]
Post subject:  Re: 1.5 DB schema
Scribe - all I am saying to you is this: watch out for Torkil. He seems like such a nice kid...then *BAM*... it'll shock you! Don't say you weren't warned, either. He just picks and picks and picks until the nicest of people fall apart. Normally. I am not this way. Amy  ;)
Author:  Scribe [ Tue Apr 18, 2006 10:18 pm ]
Post subject:  Re: 1.5 DB schema
Yeah, he kind of reminds me of me when I was young!

Keep up the good work Torkil and let us know when you're happy with what you've got. I'm trying to work my way through the API and patTemplate stuff right now and then I'd like to revisit the schema and see if I can rationalize it all in my head.

I love having a picture!!!!
Author:  AmyStephen [ Tue Apr 18, 2006 10:23 pm ]
Post subject:  Re: 1.5 DB schema
Scribe -

I have seen the amazing stuff you have done with the API and many of us are watching your work on the patTemplate stuff. So, thanks to all of you getting the development side documented. WAY cool! (Torkil is great! LOTS of fun.)

BTW, Scribe - have you seen this? It's an "old" patTemplate doc (plus some good Developer doc):

http://developer.joomla.org/sf/frs/do/v ... per_manual


Amy
Author:  louis.landry [ Wed Apr 19, 2006 6:46 am ]
Post subject:  Re: 1.5 DB schema
Good to see we have some DBA types in the community.... this will help when we rework the schema in an upcoming version.  It is important that when we make a break in the DB schema (most likely 2.0) that we have a very well rationalized and efficient schema -- I personally think 3NF would be ideal --.  When we get that that point we will be making a call for input from those who have the skills to help with this.  In the meantime, feel free to discuss areas in which we can improve the schema other than some of the obvious things.

For the 1.x series we are unfortunately stuck with the schema we have inherited and will simply have to make the best of it.  I certainly don't consider myself an expert and obviously haven't been working with them since the 60s... but i've studied them in school and even wrote a primitive relational database while in college.

Any questions on our current schema i'd be glad to answer.

Louis
Author:  torkil [ Wed Apr 19, 2006 7:44 am ]
Post subject:  Re: 1.5 DB schema
More on 3NF, good example included: http://databases.about.com/od/specificp ... /aa3nf.htm
Author:  AmyStephen [ Wed Apr 19, 2006 9:37 am ]
Post subject:  Re: 1.5 DB schema
More on the 60's: http://en.wikipedia.org/wiki/Sixties

...Thanks, Louis and Torkil
Page 1 of 4 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/