Database Design Choices

To follow up on the last article, I wanted to share some insight on a few of the decisions I made during this process.

Handling Multiple Sites

One of the decisions I had to make was whether to create a new database for every site that was created within the system or to store all information on all sites within a single database.

In the end I decided that a new database will be created for each domain that gets created. In doing so, it will be easier to track which sites are creating the most content (the database will be bigger). It will prevent cross-domain data leaking and be less prone to security breaches. It should also help improve performance since there will be less data to generate each query on. It’ll also be easier to move a database for a specific site from one server to another.

The downfall to doing it this way will be when a new module is made available or changes to the structure of an existing table is made, it will need to be replicated to all databases instead of just one. It’ll also be harder to aggregate data across multiple sites.

Handling Multiple Languages

Creating a system to handle multiple languages can be tricky. The easiest way is to define a specific number of languages that you wish to support and then create fields for each language.

Description_en
Description_fr

While this is easy to develop, it doesn’t offer much in the way of flexibility.

The way I got around this was to define a master language for the site (usually English) and then link all documents off of it. Each document would have it’s own id which could be used for links. From one object, you’d always be able to find it’s relation.

This required three fields:

Objectid
Objectparent
Objectmaster

The object ID identified the current object. It was my primary key. Object parent was a reference to another object in the same table. This was how the site hierarchy was determined. Finally, the object master was a reference to another object in the same table which indicated which object was the English version of the current document. The English version would have a master id of zero since it was the master.

This format worked somewhat well but had a couple serious drawbacks. The first was that the logic required to pull out certain objects proved difficult at times. The second was that you always had to have a master document. In larger sites where you have different languages for different regions, one section may want all documents in English and then try to translate them to French whereas another section may want all the documents in French and then try to translate them to English.

This time I’m going to try using a composite key. A composite key is where two or more fields comprise the primary key. Therefore, two values will be required to locate an object: the ID and the language. In this way, there is no longer an object master. There will still be an object parent reference to determine the site hierarchy.

Composite keys present their own hurdles such as possible performance issues as well as any reference to the object table now needs an additional reference to the language table.

Storing Interface Labels in the Database

For site administration, some would say to store text labels and translations in the database. I’m opting to not do that. Each database will store data that is only pertinent to that site. The admin interface will either be stored on disk or in memory for performance.

With the last CMS I built, I developed a resource class that loaded the translations off disk when requested and stored them in memory until the application was restarted. I anticipate that I will do something similar with this one.

Change is to be expected

I still have a ways to go with regards to really mapping out the attributes of all my entities. And certainly as I get into the think of development, I'll find stuff that works, stuff that doesn't, and stuff that I missed entirely!

Next up is Technology Choices