Joomla! Discussion Forums

I know we are stuck with the current db scheme for the 1.x series, but how about adding the reference stuff to the installation-file? From my point of view it wouldn't change anything and would help other people to create such nice diagrams like torkils.

@Amy
Interesting, they had electricity in the 60s? I heard there were only these VW busses and lots of illegal drugs...

OT: On a sidenote, Amys son sent me this picture from an interesting 60s artifact, currently residing in their family livingroom. Just goes to prove that you shouldn't listen to her funky ideas, for instance on table aliases.

@Hannes - Now, listen you!  ... We had electricity, but I was the only one worldwide with a database. I got it for my fourth birthday in 1965. We kept it a secret, but that Codd rascal discovered my toy and then published his paper on the "relational" data model in 1970. Note: this is AFTER 1965 when I began my DBA work. My family, not being litigious by nature, moved on to playing with satellite technology.

I don't know how many of our end-users would be interested in a data model. Even people I build environments for do not like to look at a data model for any length of time. If you give them a big color poster of it, they are more than happy to hang it in their cubes -- but, talk about it. Not usually. Now, I would like to see a "Getting Started - Newbie Guide" in the installation!

I think we are too nerdy, Hannes! Most ppl don't think like that! I know, it's hard to believe!

TORKIL - this is exactly what I was talking about Scribe -- watch your back! That is such a fancy flower pot that Sam brought home!

OK. I am serious now. I can see I am not going to shake the alias thing, so, let's do it. I have a few questions for you purists.

Point 1: The combo like named key elements and alias letter Fiasco.

In this example:
You are aliasing using single - alphabetic letters. (ex. "a" and "b");
You have like named key data elements (ex. customer_id = customer_id -- whether it's PK or FK);
You wind up with: WHERE a.customer_id = b.customer_id

Someone raised the "issue" that it is even more difficult to "tell them apart?"

They should be equal! What difference does it make?

Point 2: Longer aliases make for easier to read and understand code

Example 1: longer aliases

SELECT customer.customer_id, customer.customer_name, customer_contact.customer_phone, customer_contact.customer_email
FROM customer_table AS customer,
    customer_contact_information_table  AS customer_contact
WHERE customer.customer_id = customer_contact.customer_id
ORDER BY customer.customer_name DESC

Example 2: aliases that stay OUT of the mind's way

SELECT a.customer_id, a.customer_name, b.customer_phone, b.customer_email
FROM customer_table AS a,
    customer_contact_information_table AS b
WHERE a.customer_id = b.customer_id
ORDER BY a.customer_name DESC

So, Example 1 is easier to read than Example 2?

Point 3: Why alias at all?

If you are in favor of longer aliases because it's more descriptive of the table name, go for it! Use the table name. That is more consistent with the column name discussion we had. I agree with whole words - no abbrev.

++++++

Ok. Let's hear the other point of view.  Why is "a" and "b" not the RIGHT thing to do? Bring it!

Lets not drag this out too long

We just have different views on things. I use a mix of what we are discussing. Of course, an alias that has 10 letters is not a good alias. I do not use meaningless letters like "a" or "b". I would do this:

jos_content = c
jos_content_asubtable = ca (since the alias "c" is taken already)
jos_categories = cat (since the aliases "c" and "ca" are taken already)

With your method you would get this:
a.category_id = b.category_id
In my opinion its hard to identify both tables and keys in this example.

My method would result in this:
c.category_id = cat.id
Much easier

Why?
1) I can easily see that category_id is a foreign key and
2) that id is the primary key in this query,
without backtracking to find out what "a" and "b" are aliases for, since that's really a mystery if you don't know in advance. With "c" and "cat" you at least get a hint.

It's just a way of documenting the database with a good naming convension in my opinion. If you compare it to php's classes and functions, you know that "GetContent" is a class and "getContent" is a function... merely because of the capital C.

All right. I think I get your point. On the new 2.0+ schema, would you recommend, then, table names AND perhaps a standard alias for each table? For me, that would resolve the debate.

One can always write guidelines, but in the end it's up to every developer to read and follow them. When one is looking to make progress quick, corners are often cut

I'd be glad to participate in a standards discussion, but it sure sounds like the dev team is well equipped with DB people to make those decisions already.

I would agree with torkil...

I get sucked in often to using aliases such as a and b...  this makes code harder to read...

but I would speak more strongly on the point of naming foreign keys and primary keys.  I think it is good to name primary keys id and foreign keys table_id.  Yes, they are equal, but if you do end up using aliases such as a and b, it helps to differentiate which table is your primary table and which is your secondary table - I know this is bad terminology, it has been a long time and I don't have formal database training, although scarily enough, I did teach databases, albeit to people who struggled with the basic concepts, and only two courses, but that was a long time ago...

If my query looks like:
SELECT *
FROM jos_mycomponent AS mycomp, jos_categories AS cats
WHERE mycomp.cat_id = cats.id

Then I know that I am getting a list from mycomp and looking up the category information from the categories table.  IMO, at least, it is clearer.

Ian

Any standard is welcome in my book, and anything is better than the current state of things, where both id, section_id, sectionid and section are used for describing columns that refer to jos_sections.id.

Again: This is of course a legacy and compatibility problem.

We should probably start < http://www.ansi.org >

Does anyone else find it odd that -- THE international database standards organization offers a download of their standards education database and it is an EXCEL file?  I *hate it* when people call their excel files their databases. But, I can easily tolerate it with accountants.

First link on this page:
<  http://www.ansi.org/education_trainings ... x?menuid=9 >

I liked very much what Torkil said. It has been very impressive to me  and thanx for the 1.5 DB schema, it was what I was searching... 

I've previously used a tool called Case Studio (unfortunately not open-source) towards this purpose. After making a schema (also possibility for reverse engineering) with the tool, you are able to convert the schema to other database schemas and generate the DDL from those. It worked great, the only problem was that it wasn't able to generate DDLs directly from the one schema, you were forced to convert the entire schema before generating the DDL.

Support for MySQL ISAM tables is another issue because there is no support for referential integrity. MySQL 4.0.1 and later releases do support INNODB tables with integrated support for referential integrity. A lot of hosts are still using 3.x versions of MySQL. With referential integrity, the mapping of primary and foreign keys is handled by the database engine internally, so naming conventions are less of an issue. A lot of naming conventions have been developed over the years. I'm in agreement that it is important to select one convention and stick with it for consistency reasons across the core and all CMTs.

Hi torkil,

Most of your comments and the discussion in this thread in general, are perfectly correct in respect to what the perfect db schema should be. It is obvious that the database has some excess baggage that has accumulated over the versions.

In reviewing the db schema for Joomla! 1.5 it is necessary to keep overall system constraints in view. The primary consideration that has impact on the db schema is the need to keep a relatively high degree of backward compatibility. The easiest way to maintain this this would mean no changes to the db schema at all between 1.0 and 1.5.

The magnitude of code and functional changes have, naturally dictated some changes to the db schema. "So... if we have changes... why not go the full nine yards and tidy up the db while the going is good"?

Obviously there is a balance that needs to be found if a high degree of backward compatibility is dictated for Joomla! 1.5. Where the line is drawn is something that can be argued and debated and there is no "schoolbook solution". I will try and explain the guidelines that we have tried to keep regarding the db schema and "drawing the line"

The basic assumptions having impact on the db were:
- There is no upgrade path from 1.0 to 1.5 but there needs to be a reasonable data migration path. We will provide an automatic migration for core content but the manual migration process should be as simple as possible.
- We don't want to break 3PD components where we don't have to.
- Joomla! 1.5 will use a single encoding - utf-8. This requires conversion of migrated data and changing charset of string fields
- We are staying with MySQL only in this version.

From this the general guidelines regarding the db:
- It is OK to add fields as long as the old content records will be OK with the default values of the new fields. This does not have adverse effect on migrating data.
- It is OK to drop an entire table if it does not effect the functionality of migrated data.
- It is NOT OK to drop individual fields as this complicates migration. It would require parsing of the dumped sql file or creation of temporary tables to reconstruct the records. It would also complicate manual migration.
- It is NOT OK to change field order as 3PD components may use the index of fields to access them.
- It is NOT OK to change field names as 3PD components may break and as it complicates manual migration

We have been almost completely successful in following the above. There have been 3 field name changes in the acl tables due to the fact that we have applied a new library and decided that it is better to touch the db than hack the library.

To sum up, let's remember that Joomla! 1.5 is halfway between 1.0 and 2.0. The primary objective has been to apply a new framework, localisation and utf-8. All this together with backward compatibility. The db will be centre stage in version 2.0.

For being a coder out of the 50s I want to give my $0.05 to this thread ... 

A common naming scheme would be fine because it's a real pain in the a* to check what's going on in foreign code without it. Style for itself doesn't matter that much as long as there is one.

IMHO very much more important is to take data integrity/security, performance and portability into account when thinking about normalization.

My experience in now more than 25 years of database related coding is:
Don't do to much normalization. Negative impacts are in data integrity (a small "hick-up" is enough to loose data), non-portable code, problems with maintenance of code and a significant loss in performance.

It's similar like programming for web environment and, as special case, taking CGI environment into account:
Instead of having dozens of statements assigning data to a variable and lowering number of echo statements as much as possible leads to much more performance and reduces load of server very much.

Do a stress test (100++ requests/minute), wait 5 minutes and you will see what happens i.e. with Joomla and servers in use. 

cu, diri

@Diri - Excellent comments. To add to the data integrity issue you raise, we need transactions -- or "transaction like" equivalent actions -- to ensure all necessary database actions have taken place before a commit.

@David - your criteria for how and when you can change the database amaze me. I am impressed over and over and over with the thought that goes into Joomla! Breathtaking. Blows me away.

Amy

Amy,

thank you for your agreement. But, transactions only are a "nice to have" in case every DBMS being used supports them. For real independancy (portability) especially in web environment coder has to implement kind of transaction system for itself. Be aware of the fact that most web sites don't run db service on same machine like web server and you see the bottleneck related to secure data storing.

About increase of performance coding it for yourself I could mention a POS/warehousing system being developed by myself in middle of 90s. (it's still maintained). I didn't use any specific capabilities of db system in use at some customers places, some customers use Access as frontend and backend with up to ten clients working at the very same time. VBA coding is such independant that even a P120 with 80 MB RAM is sufficient to work as POS client as well as server for further workstations (remember: MS Access!).

Don't ask me what I think about such a constellation but, customers are informed about the risks and it works very well since many years. Backup, backup, backup, ...

On the other hand this system can be used with different DBMSs (I use DB2 for development) and in web environment, such frontends exist as well.

First issue to get rid of with !Joomla to have chance for easy porting is using autoincremented fields to hold reference to related data. Portable programming in relation to DBMS means to be able to transfer data from one DBMS to another one without loss of integrity and functionality. It doesn't need very much, one table at least to hold last ids used and very few functions ...

cu, diri

ps:
Editor in use here in forum has a bug:
Formatting a word in italic enters start tag before and behind word. Browser is Seamonkey.

Diri -

You have another wonderful database mind. This thread is by far my favorite in all of JoomlaLand. There are a many great DBAs here. 

Note my "transaction like" equivalent actions phrase -- I worded it that way because of the point you very nicely expound upon when you propose that the "web environment coder has to implement kind of transaction system for itself." This is an excellent description of what is needed and will absolutely help to ensure portability to various RDBMS.

Good points on replacement of autoincremented fields, too. Anything specific to one RDBMS environment has to be removed or the various protocols for each RDBMS have to be developed. Easier one way that works for all - than many ways that only work for one.

I read the SoC DB thread awhile back. What they are looking at is amazing stuff -- blows me away. They went through several RDBMS environments and identified how specific functions were implemented in SQL. So, we are in good hands, that is for certain! They know what they are doing!

I enjoyed your MS Access, comments, too. I can see you have "real life, corporate" experience. There is a LOT of Access databases out there. And, for many tasks that must be done, MS Access is a perfectly acceptable solution.

Frankly, I would have a difficult time identifying another database so readily available at the desktop and easy to use for a non-technical community. It allows many people entrance into a better storage system than a spreadsheet, and therefore moves corporate data one step closer to an integrated, enterprise-wide database.

It also provides a decent frontend tool for a non-technical person who needs to interact with data. Me, I prefer a blank query window and I will type in my own SQL, thank you very much. But, let's face it, I still find myself from time to time dropping down to a DOS prompt, too, in order to execute a system command. That gets me no respect whatsoever from the younger crowd raised on Windows! 

Thanks for your comments! Glad you are part of Joomla!
Amy

Amy,

nice to see somebody having similar mind when it comes to coding principles. 



I'm aware of many very experienced people in community of Mambo and !Joomla.


Excuse to not having set a detailled pointer on your remark. Web environment is such a specific environment ... coder's first and main enemy is back button in user's browser, second one is problem of secure storage of data. The rest is (more or less) trivial.


Have a look as it is now:
Even many extensions use mySQL specifc functions instead of !Joomla's API. This makes "porting" even more difficult for having the need to not only change !Joomla (not very much, btw) - each extension in use has to be checked and changed in case of such (not so intelligent) coding.

I'm sorry but, I don't have the time to follow each and every interesting subject related to !Joomla.
Nevertheless, in case somebody wants to make a common layer for most prominent RDBMSs in SQL only (it is possible in most cases) IMNSHO he is going the nowadays usual way: Save time (and brain) of coder, no matter what it takes at runtime.

Due to some time being responsible for DBs and web servers in ISP environment my mileage might be different compared with other experiences collected in "clean room" environments like companies (not to mention universities).

That's absolute correct. And, it's of little interest what I like or not: When it becomes a little more complicated it's kind of perfect money making machine. 

Err??

.mdbs should not be used as backend in environments where more than five users are working at the same time. When there are 10 or more users using Access as backend is negligence in high degree.

Using Access as frontend is somewhat expensive but fine (esp. for me as coder ).

You look like being one of those stoneaged people like me who want to know what happens instead wild clicking on pictures only.

Now, let us wait and see in which direction coders of !Joomla decide to go. Related to DBMS they have only two choices:
Either getting rid of dependancy of mySQL or being stuck with it.

The faster they decide to get rid of this dependancy the faster I become a more happy camper with !Joomla.

But, it's not the one and only issue for me.

The deeper I dive into !Joomla the more I find issues which are not according my taste. I.e. I want to have a real sitemap and a correct pathway at every page (one leads to the other like !Joomla works now) without blowing up menu manager with faking un-maintainable entries.

Quality of code for itself is fine but general coding is very inefficient in many cases (see example of echo statement).  This should be improved as well to not to run a site and getting a DoS-like situation for having some more concurrent users. That's another problem for me while being an old-style bit-counting performance oriented  coder ... 

BTW:
Most prominent extensions to cause such a DoS-like reaction of servers are e-commerce extensions like Oscommerce or Virtuemart. They can cause denial of services for inefficient usage of SQL as well as by inefficient use  of PHP in web environment very easy. Do some stress tests for yourself (more than 2 requests/second), you will be astonished what kind of hardware you would need to reliable serve more than 500.000 page impressions per month (views are much more).

cu, diri

Your comments are so cool. You are touching on the same major points that I hear the developers discuss when they are talking about the future roadmap and the SoC work.

I think they use the SoC resources (incredibly smart young minds) to help envision and detail out our future. And, that is an EXCELLENT use of these gifted people.

Related to cross-database support:
See < http://forum.joomla.org/index.php/topic,58460.0.html >. It's another favorite thread of mine -- I realize you do not have lots of extra time, but, when you do, try to read through it. Especially pay attention to the external link FaceDancer provides in Reply #7.

This is also a good read: Summer of Code < http://dev.joomla.org/content/view/1552/92/ >

Your other concerns ( i.e., sitemap, correct pathway, menu manager with faking un-maintainable entries) all relate to the disastrous Itemid data architecture. Boy, that was a bad idea on someone's part! < lol > Last I heard, the roadmap is lining up nicely for uncoupling from this dependency with v 2.0.

< And, BTW, over the past 25 years in this industry, I have found that I have made LOTS of bad data architecture ideas, too! I have about 1/10th of the intelligence of your developers -- and that is on a GOOD day! >

You have your finger on all of the good stuff. It is great to see such intelligence in our community. I am very pleased to have you with us!

Than there's hope they don't take my arguments as offence and they know what we are talking about as well.

I had a short look at it and have the feeling to rephrase some already known wishes of community ...

BTW:
To get rid of most disturbing feature (autoincremented fields) to get better data integrity *without* additional efforts it should not take far more than 2 hours for somebody being really familiar with !Joomla's core.

You are very polite ... 

And ... what do we have to use *now*?


I'm aware of this but, OTOH you've kind of clean room environment there. Buying additional machines to get "performance" back is not *the* factor taken for decision making. Have a look at SAP. R3 is leading software world wide, R2 has been far better IMHO without having really that much lesser functionality. It was more stable, more platforms where supported, less requirenments, more easy to maintain, .... TCO are significant higher for R3 than for R2. Who cares?

Web is another world. You can't sell another server that easy. You would also need software being able to use different db servers and clustered web servers (think about desaster with www. and !www.).

People (customers) need something now and it should be sufficient for some time. In very first moment it has to be small, fast and comfortable. The almost only real decision making factor beside price very often is ease of use for people being to lazy to learn some fundamentals. How do you teach such people that there is need to have faking menus, content is shown as blog, there's no real CMS but a blogging software, ...?

Don't get me wrong but, I fear time needed to change !Joomla that much will be vey long. Watch internationalization which could have been started in admin part as well with v1.0-series for simply moving hardcoded strings to a language file one after the next while changing code. There would have been no real programming envolved but !Joomla administration might be available in multiple languages while being maintainable now. Not the best solution but, a solution to make best of what already exists without real effort.

cu, diri

Diri -

You are issuing the one challenge that even our developers, no matter how brilliant and dedicated and energetic, no matter how much they would want it as well, will never be able to overcome -- and that is providing everything we have talked about NOW.

Cannot be done, my friend, it simply cannot be done.

So, here we are - together as one - Joomla! - a community. And, you and I, just two people of 42,000 community members have engaged in a little brainstorming session that only geeky DBAs would even appreciate and we have identified goals that we would like to see implemented for Joomla!. And we know that even those goals, if they are selected by the core developers, will take time to implement.

I made a decision to be a part of Joomla! and to be positive and hopeful and involved. Sometimes that requires patience, but most of the time, my sincere gratitude for what I am given, free of charge, and the contributions these great, young minds provide the world just blows me away. It really does.

And, to know that this open source movement is going on for so many other technologies: desktop applications, networking technologies, servers, database environments, well, you name it, they are making it! It is difficult for me to contain my enthusiasm for what this will do for developing countries and rich countries; for children and old people; in areas like education, healthcare, and world peace. Amazing! It's simply amazing. And, Joomla! is a part of this!

I hear what you are saying that we have a ways to go. I hear your passion, Diri, about "being there now." I do understand. I really do. But, we must recognize the limitations of time and we need smart people like you to contribute to discussions of our future and pitch in where you can to speed things along.

We are best served if we can "think positive" about our future. Look for the good! Look for hope! Look for things that ARE working! Look for possibilities! Look for answers! That is what we need! It's easy to find the fault -- especially for people in our profession where we are trained to find problems.

It is those people, Diri, who can articulate real solutions to problems, and find appropriate ways to present their ideas to others, and are able to win support, and willing to invest their own time and effort to implement, who are going to make a real contribution and difference.

Today, the developers are tired and have worked hard for a long time. Getting v. 1.5 out the door is the only solution. And, we need to pitch in for that where we can and we need to encourage their efforts.

We will talk, again, and it is great getting to know you, such an honor, Diri. Thank you for sharing your voice.
Amy

Amy,

thank you very much for your comments.

About factor time and "cannot be done":
I'm working in web environment since several years as well as in industry (industry more than 30 years). I do have "my own" CMS which is Perl and / or PHP based. It can serve multiple sites. It can produce static HTML and it is capable to use SSI as well. It is able to perform all the jobs needed for a magazine, blog, wikki, ... . A very fine role and user based security system is implemented as well. It is capable to run the backend in "closed" intranet as well as in "parted mode" or "online mode". As a result it is very scalable and offers real good performance with very little load of servers: More than two million page impressions per month are no problem on a single relative slow machine even when db service and backend run on very same machine. So far, so good. One might think it is the ideal product.

Now, I'm no salesman but a technician and kind of realistic. The drawbacks:
User's and admin's interface is some shell scripting and command line as well as a web interface. All from a coder for a coder. It's all but nothing for "Joe User". A not so polite description would be "unusable".

Looking further I'm not able to watch each and every upcoming security message related to any programming language and software. I'm simply unable to maintain it the way I should do it. Here you have those factors "time" and "cannot be done" as well.

Sad enough there are some contracts which don't allow to make the beast open source which bring factor "make money" into the game: Why should I put more efforts into it than customer pays?

While watching market (open and closed source) since many years I decided to jump the waggon of some systems where I hope they are / will be able to fullfill my needs and those of my customers. In winter 2004 / 2005 I re-evaluated Mambo - now I'm at !Joomla. It is one of those products I'm watching and exploring in more detail. And - to be honest - I'm already looking how much effort it takes to get rid of those issues which are most annoying for me. This work started with Mambo but, because of !Joomla being a moving target, has been stopped for some time. Maybe, when 1.0 series is more stable there can be a really hacked version. At the moment I fear there are problems caused by side effects which should be solved first. Time needed for this gives more time to me.

cu, diri

Very nice analysis, Diri. I think you are right about waiting until the 1.0 series is more stable -- and, I think that's coming soon with v. 1.5. The thought that you have put into all of this is amazing. Thanks so much for sharing. It will be great to have you with us as this starts to roll out. Amy

Nie work, printed out. Thanks a lot! 

Torkil, could we get this DB schema linked somewhere on the developer network ?

Torkil,

Thanks for your great work in decyphering the abstruse Mambo/Joomla database schema. I started to look at this a few months ago after getting Joomla running with the SMF forums. I found that the User Manager in Joomla was only showing me a few of the site members - it still is!

I started to try and unpick the database, not having found anything detailing it. Eventually I found your schema a couple of months ago. I've not done much since then, and still have the User Manager issue.

In the schema I think there are two important relatuons missing that affect the Usder Manager's view of the users. There's certainly nothing here to stop problems arrising!

I think that jos_users.id should be related to jos_core_acl_aro.value - probably 1:1, and that jos_core_acl_aro.id should be related to jos_core_acl_groups_aro_map.aro_id, again probably as a 1:1 relationship.

Here's a couple of records that don't show in User Manager:


 
    jos_core_acl_aro.aro_id
    jos_core_acl_groups_aro_map.aro_id
    jos_core_acl_aro.valuej
    os_users.id
    jos_core_acl_aro.name
 
 
    14
    14
    66
    698
    Paul
 
 
    15
    15
    67
    455
    Bill
 


And one that does show:


 
    jos_core_acl_aro.aro_id
    jos_core_acl_groups_aro_map.aro_id
    jos_core_acl_aro.valuej
    os_users.id
    jos_core_acl_aro.name
 
 
    10
    10
    62
    62
    Admin
 


Here's to a documented schema for the next release - it can't be beyond the developer's capabilities to produce a database update script to move from one schema to another - can it?

Regards
Mikre

mikre,

you will be out of luck when trying to dive into Joomla! with database structure as starting point.

When exploring database one would think almost everything is fine. But: What you see there and what Joomla! is doing at runtime are different kind of beasts. Your problem seems to be related to one beast, ItemId is another example.

There is nothing really wrong with database structure, problem is workflow of Joomla! for itself. Even better ACL management is possible with existing db structure.

Fixing ItemId issue and getting rid of autoincremented field values used for references would shift Joomla! into professional  class because than it would also be possible to run backend at other (db) servers. Capability to split frontend from backend is one of most characteristic features of professional CMSs being used in not that small environments.

This can not be achieved with reasonable efforts as long as autoincremented fields are used the known way. Also using backend code in frontend or vice versa is understandable but, which files are needed to have backend or frontend only?

It has been set up now. You'll find the link on this page: http://dev.joomla.org/component/option, ... Itemid,32/ under 3. Specifications.

For us simple folk, willing to learn:
what is wrong with autoincremented field values?