Database design for reusable functionalities

Need Help
1

Hello there.

This time, I ask for a best practice - also related to cakephp.

Let’s say, I have specific functionalities (a tag system, a rating system, and so on) and different subjects that can be tagged (articles, documents) and rated (products, items).

Regarding the functionalities, I would create cakephp-plugins (so that I can reuse them whereever I want).

But regarding the database: Is it better to created tables per subject (articles_tags, documents_tags) or would I create one base table (e.g. relations_tags) with a column that describes what subject has been tagged like id | tag_id | subject_id | section ==> 10 | 3221 | 42212 | documents

I tend to this implementation, but can you give me advices, tips, things to think at or best practices.

Thanks in advance!

2

As long as you keep an eye out for diverging requirements as the system evolves I think the one-table-serves-all approach that you prefer is the way to go.

You can even use association aliasing in the served Table to keep everything clear.

Example:

I have a system with Addresses, People and Facility entities. The people vary by ‘role’ but all share the use of Addresses as a list of shipping addresses. There are 3 different types of Facilities and each has a different table because of structural differences. But they all have a single Address, the physical location.

So, my address schema defines 4 foreign keys (as you propose for Tags).

The association in the Person Table is:

$this->hasMany('ShippingAddresses', [
   'className' => 'Addresses',
   'foreignKey' => 'person_id',
]);

The association in each facility variant is

$this->hasOne('OfficeAddress', [
   'className' => 'Addresses',
   'foreignKey' => 'person_id',
]);

This has been giving me named uses of addresses in the different parent contexts that reminds me what I’m dealing with; single address or set of addresses.

echo $this->element('address_fieldset', ['address' => $person->shipping_addresses]);
echo $this->element('address_fieldset', ['address' => $warehouse->office_address]);

But it also is keeping my code nice and DRY because Form templates, validators and rules all funnel down to a single Address class (and its subordinate records like Coms which holds phone, email, etc contacts for that address).

Further details about handling diverging needs

I’m just beginning to see cases where the behavior of Addresses needs to vary between use cases. This is where the one-for-all approach might fall apart.

According to my business logic ShippingAddress displays carry edit and delete links in all views but an OfficeAddress is always edited as part of its owning data and can never be deleted. So I need rendering-variation.

In this situation I’m in a template with an $address variable and no knowledge about the kind of address I have. But I have several tools that can save the day.

Solution 1: Two Elements

Make and use a different element for each situation. If I build them properly I can keep my code DRY.

echo $this->element('shipping_address_fieldset', ['address' => $person->shipping_addresses]);
echo $this->element('office_address_fieldset', ['address' => $warehouse->office_address]);

Solution 2: Entity Introspection

I can add a method like is($type) to my Address entity. Then the single view element can alter its behavior:

<!-- BEGIN address_fieldset -->
<?php

//output an address
//output the coms
if ($address->is('shipping') {
   //output the edit/delete links
}

?>
<!-- END address_fieldset -->

Even more extreme divergence

Cake makes it easy to adapt to even more use-case divergence.

I can imagine that my single office address code might settle in as the default address case; simple view, simple fieldset element, no tool links.

Shipping addresses may need a variety of enhancements that I don’t want in the basic address table and entity class. In this case I can leave my db schema as is and make pair of new class

class ShippingAddresses extends Addresses {
   //special table implementation
}

class ShippingAddress extends Address {
   //special entity implementation
}

Now adjust the associations and you’re ready to go.

// PersonTable association
$this->hasMany('ShippingAddresses', [
   'foreignKey' => 'person_id',
]);

//Facility variant associations can stay the same
$this->hasOne('OfficeAddress', [
   'className' => 'Addresses',
   'foreignKey' => 'person_id',
]);

The big bonus here is, because the association was already called ShippingAddresses, this change would not change any code!! Cake names the entity and associations automatically based on the association alias. Changing the details of that association does not change the sequence of names!

Conclusion

As long as your table schema doesn’t diverge between uses cases by more than a minor field or two, I think one-for-all is maintainable

3

You can create migrations in plugins. So that there can be only one table in the system, eg. DatabaseLog. In other plugins or in the App you can link to those tables

// src/Model/Table/UsersTable.php
$this->hasMany('DatabaseLog.Logs')
    ->setForeignKey('user_id');
// points to plugins/MyPlugin/src/Model/Table/CustomsTable
$this->belongsTo('MyPlugin.Customs')
    ->setForeignKey('user_id');

I don’t know if an App could overwrite Migrations, I doubt it, but you can extend a plugin table in your app, and use that in the app.

You can use Behaviors, in which you may require the table to have certains columns, eg. CounterCache or STI

You can use a mixture of both to create belongsToMany relationships and share code.

4

Hello to both of you.

Many thanks for these great and detailed answeres. So I’ll go with this solution. When I start to implement these features next week, I’ll re-read your answers. Thanks again, well done :wink: