How to create mysql table with many hasMany association in CakePHP?_问答_开发者

I'm defining a completely new database. I have now faced a problem which I would describe as "usual" but still could not find good information from web. So here's the problem:

I have many tables in my database (which I would describe as guides) such as:

Skills
Places
Activities
and so on...

Now to all these guide types I'd like to add a comment feature and other similar features like attaching images and videos. I have many guide types so I dropped the idea of having a separate comment image and video tables for each of them. I need one table for each of them.

The question is, what is开发者_运维技巧 the best way to achieve this? I have heard and read about 3 solutions and I'm not familiar with none of them.

I have read about using UUIDs would fix this problem but I'm not very familiar how they function. Could someone elaborate on that if that is the correct way to go? Something about UUIDs I read but not quite understood it.
Other thing I have read about is creating a hierarchial model "tree table" which would hold association links. More info at Managing Hierarchical Data in MySQL.
I have also read about creating object tables and using program like object inheritance inside MySQL in a similar way like the hierarchical model.

UUIDs sound most simple so I would appreciate help in there.

I don't know anything about how to use them. But here's how I thought it works - at least you'll get a hang of it what I'm trying to achieve here and how/what I'm misunderstanding about them:

I would create a new table: Guides which could have UUID field.
Then link all those guide types (Skills etc.) to guide table (Guide as parent and the other as child)
Parent and Child have both UUID fields and when creating a guide Parent and Child gets same UUID so they can be linked. Child also has its own Id field.
Then link comments to Guides by using UUID field that points to Guide plus separate id int field for comments.

Please tell me if this is correct way or is it total garbage and if so, how I should do it?

Have you though about using a normal hasMany relationship with a condition? Read about it here.

class Skill extends AppModel {
  var $hasMany = array(
    'Comment' => array(
      'className'     => 'Comment',
        'conditions'    => array('Comment.type' => 1), // 1 for skills, 2 for places etc. or something like that.
      )
    );  
}

Check http://cakeapp.com, create your DB layout there and download the SQL later.

I read more about UUIDs and since they allow application wide unique IDs I was able to do "inheritance" style of database.

I used my own prefix at the start of the every table name to avoid reserved table name collisions such as object. You can use any kind of prefix, for example: my_ and to use it like: my_object. All tables should use prefixes in this example.

So I created table Objects. It has the id field with Binary(36) type. Cake recognizes it as UUID field. Then I used 1:1 identifying relationships and inherited other tables from it, which I wanted to interact with others.

So I created 1:1 identifying relationship to Comments, Videos, Pictures table so that the table had the identifying foreign key being also a primary key.

Then I created Mappings table to which I used two 1:1 non-identifying relationships without primary key. This means this was really HABTM relationship to self.

Now this let me to "inherit" other tables from Objects table, like News table with again 1:1 identifying relationship. Then it was possible to link Comments, or anything other that has the 1:1 identifying relationship to Object, to News table by using the Mappings table.

I hope this will help others who are pondering this kind of solution aswell.