How about storing single records and their categories in one table

Question

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?

Answer 1

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!

Answer 2

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