2

I'm developing my own component for Joomla. I know that in many components single records and their categories are stored in different tables of the database. But how about storing them in ONE table using parents? Each record and "category" has title, some content/description, metadata, parent field and other data in common. Parents for records are their categories and parents for categories are their parent categories (if needed). And in my table there is the column type (=record or =category) and I can just operate them in my views. This way minimizes sql-queries and number of models/views. I can understand that it's a new and maybe strange idea but is it good to simplify the things in such way and what problems may be appeared in the future?

UPDATED: And if storing the relations in such one table is a bad idea how about storing titles, content, metadata and other data in common in one table but only parent's relations (id, parent_id) in another table?

  • 1
    What you are describing is actually a more logical way to use nested sets, but it would require a major shift in the current category system. Joomla uses a Tree/Leaf concept where the categories represent a tree and actual article or contact are leaf nodes that related to the category table via foreign key ref. however as you mentioned there isn't much difference between a category and an article as far as the table structure is concerned, so making the article table a nested set table and allow articles to form a content hierarchy would be more logical. My2Cents – Mathew Lenning Apr 15 '15 at 8:49
3

According to SQL theory, a 1:N relationship has to be represented by a table. In this model, any query can navigate the information in a consistent way.

Forgetting the theory, you can store the relationship values into a field, for instance as a Json-encoded array. However, you are going to need someway to parse this "multivalued field" to manage it. In LAMP, it is not possible in MySQL and you would have to access it via PHP.

To explore alternative ways to represent an Object: Jooctrine - Doctrine ORM in Joomla!

  • Ok, thanks. Then how about storing titles, content, metadata and other data in common in one table but only parent's relations (id, parent_id) in another one? – stckvrw Apr 14 '15 at 6:21
  • That's perfectly fine. – Anibal Apr 14 '15 at 9:49
  • You could also look at Joomla's UCM (unified content model) which stores titles, content etc in its main table and the references another table for content specific data. Its a little complex to understand, and is slightly clunky to use, and I'm not sure where J is going to go with it in the future (but its already used by Joomla when you start to use the content history option) – Rob Clayburn Apr 16 '15 at 9:56
3

I would really not recommend doing that. Joomla runs of relational databases (mySQL, postgre etc) which are optimised to handle relational tables. What you are describing is more in line with using a noSQL database (mongo, couchdb) which store documents that often consolidate hierarchical data. Joomla doesn't natively support such databases and as such I wouldn't recommend using them in conjunction with a Joomla site unless really necessary.

I would go with a classic relational database design. Joomla has a #__categories table and categories classes (and views I think) already set up for you (take a look at com_content or com_weblinks to see how they are used). They also have the advantage of use nested sets rather than a simple parent/child relationship (http://en.wikipedia.org/wiki/Nested_set_model).

I'd then test the queries generated - I would be highly surprised if they are the bottle neck in your application. First of all turn on Joomla's debug option so you can see the queries being generated and the time taken to run them. Then if you see any slow queries use mySQL's EXPLAIN to work out what is happening inside mySQL for that particular query. 9 times out of 10 if there is a performance issues it is because your database is missing an index. Using EXPLAIN should help you figure out if that is the case.

If you are seeing a lot of duplicate code between views/models etc then the place to handle that is in the application itself, not in the database design. So for example you can write an abstract view class which would consolidate your duplicate code and from which all your other view classes would inherit from.

To summarize I would say - design a relational database, get your app working, then test IF there are any speed issues. Its always easier to fix something that worry about possibly having to fix a theoretical problem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.