CODEWITHSUNDEEP
phpoopormpdodatamapper
developer10
developer10

Reputation: 1500

Backend app in OO PHP: Structuring classes/tables efficiently

I'm currently working on an app backend (business directory). Main "actor" is an "Entry", which will have:

- main category
    - subcategory
        - tags (instead of unlimited sub-levels of division)

I'm pretty new to OOP but I still want to use it here. The database is MySql and I'll be using PDO.

In an attempt to figure out what database table structure should I use in order to support the above classification of entries, I was thinking about a solution that Wordpress uses - establish relationship between an entry and cats/subcats/tags through several tables (terms, taxonomies, relationships). What keeps me from this solution at the moment is the fact that each relationship of any kind is represented by a row in the relationships table. Given 50,000 entries I would have, attaching to a particular entry: main cat, subcat and up to 15 tags might slow down the app (or I am wrong)?

I then learned a bit about Table Data Gateway which seemed an excellent solution because I liked the idea of having one table per a class but then I read there is virtually no way of successful combating the impedence missmatch between the OOP and relational-mapping.

Are there any other approaches that you may see fit for this situation? I think I will be going with:

structure. Relationships would be based on the parent IDs but I+'m wondering is that enough? Can I be using foreign key and cascade delete options here (that is something I am not too familiar with and it seems to me as a more intuitive way of having relationships between the elements in tables)?

Upvotes: 3

Views: 160

Answers (1)

Félix Adriyel Gagnon-Grenier
Félix Adriyel Gagnon-Grenier

Reputation: 8885

having a table where you store the relationship between your table is a good idea, and through indexes and careful thinking you can achieve very fast results.

since each entry must represent a different kind of link between two entities (subcategory to main entry, tag to subcategory) you need at least (and at the very most) three fields:

  • id1 (or the unique id of the first entity)
  • linkid (linking to a fourth table where each link is described)
  • id2 (or the unique id of the second entity)

those three fields can and should be indexed.

now the fourth table to achieve this kind of many-to-many relationship will describe the nature of the link. since many different type of relationship will exist in the table, you can't keep what the type is (child of, tag of, parent of) in the same table.

that fourth table (reference) could look like this:

id     nature     table1    table2
1      parent of  entry     tags
2      tag of     tags      entry

the table 1 field tells you which table the first id refers to, likewise with table2

the id is the number between the two fields in your relationship table. only the id field should be indexed. the nature field is more for the human reader then for joining tables or organizing data

Upvotes: 1

Related Questions