Kyareshi, I'll take you up on that offer to help with database design now.

Linkage challenges:

[list]Sections can have any title
*Every title can have multiple translations
*Every section can have multiple translations
*Each section can be assigned to only one character or one publisher[/list]

Other Requirements

[list]Sections can be locked
*Sections can be marked to be rendered differently
*Titles can be locked
*Titles can be marked default, extra, render right, render left, bold, red, blue, etc
*a history of all changes is logged[/list]

So... how can this be made to work? I've reworked my DB design 3 times now... each time running into another problem.

As of morning, Feb 24th... here's how the tables look:

mysql> explain stitles;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| langid  | tinyint(3) unsigned | NO   |     | NULL    |                |
| status  | tinyint(3) unsigned | NO   |     | NULL    |                |
| tclass  | tinyint(3) unsigned | NO   |     | NULL    |                |
| section | int(10) unsigned    | NO   |     | NULL    |                |
| title   | text                | NO   |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

mysql> explain sections;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| langid  | tinyint(3) unsigned | NO   |     | NULL    |                |
| status  | tinyint(3) unsigned | NO   |     | NULL    |                |
| stype   | tinyint(3) unsigned | NO   |     | NULL    |                |
| uid     | int(10) unsigned    | NO   |     | NULL    |                |
| date    | int(10) unsigned    | NO   |     | NULL    |                |
| cid     | int(10) unsigned    | NO   |     | NULL    |                |
| pid     | int(10) unsigned    | NO   |     | NULL    |                |
| content | text                | NO   |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

mysql> explain sectionshistory;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| langid    | tinyint(3) unsigned | NO   |     | NULL    |                |
| status    | tinyint(3) unsigned | NO   |     | NULL    |                |
| stype     | tinyint(3) unsigned | NO   |     | NULL    |                |
| sectionid | int(10) unsigned    | NO   |     | NULL    |                |
| uid       | int(10) unsigned    | NO   |     | NULL    |                |
| date      | int(10) unsigned    | NO   |     | NULL    |                |
| cid       | int(10) unsigned    | NO   |     | NULL    |                |
| pid       | int(10) unsigned    | NO   |     | NULL    |                |
| content   | text                | NO   |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+

For those curious, the first place this feature is going is under the chart of all anime/game pages. We'll have a nice section "Plot Summary" that members can add, so as you browse the DB, you might find something worth watching/playing.

[ Edited Feb 24, 2009 ]

私は日本語を少し読むと書くをできます。あなたも？

The first thing that occurred to me as I looked at your desctipion is that titles and sections share many qualities. If you think of sections as "titles under titles", and design your tables accordingly, you get a system that can store infinite levels of sections. For example, if you structure your sections table similar to this (ignore the fact that my column names differ from yours – I'm just trying to demonstrate something):

SECTIONS
Id
PublisherId
ParentId
Caption
Content

Where Id is the primary key (auto-increment), PublisherId is a foreign key (link) to the publishers table (so as to indicate for which anime/game this section is related to if you're planning to do that) and ParentId is a foreign key to the Sections table itself, tying the section to its parent. Caption would be the name of the section. Obviously you can add whatever other columns you want, but bear with me here. As an example, say you had a wiki page for ARIA The NATURAL with the following section layout:

ARIA The NATURAL
1. Plot Summary
2. Characters
2.1 Main Characters
2.2 Minor Characters

The records for this page would look something like this:

Id, PublisherId, ParentId, Caption, Content
1, 600, null, 'ARIA the NATURAL', 'Aria is an anime blah blah blah.'
2, 600, 1, 'Plot Summary', 'Stuff happens.'
3, 600, 1, 'Characters', 'There are many characters blah blah.'
4, 600, 3, 'Main Characters', 'Akari, Alice, Alicia, etc.'
5, 600, 3, 'Minor Characters', 'Postman, Dude A, Dude B, etc.'

Hmmm. You might want to include a field for ordering the sections. You know, a number to ensure that the "Characters" section doesn't pop up before the "Plot Summary" in the above example. These values would all be manipulated by the PHP, of course – the user would never see them.

As for keeping a history, the first choice you have to make is whether you're going to keep the history in a separate table, or if you want to keep it and the current records in the same table. Separating them might make your queries marginally quicker, but it creates column duplication (which might be a pain to manage later). I would recommend keeping them in the same table (but of course make sure this table is properly indexed).

Going by that, you would want to store information about the version of the section. This might add the following fields to your hypothetical Sections table:

SECTIONS
Status
VersionDate
EditorIp
EditorComment

The Status field is important, because while all edits and additions are recorded in the table, there is no way to tell whether a section has been deleted. Thus, when a user deletes a section, it is recorded in the section status. VersionDate is important to keep track of when edits were made. Also, when displaying a wiki page, you would alway select the most recent section records (order by VersionDate). I added EditorIP for identification, but you could also store their user ID or whatever information you have on them. Lastly, EditorComment is just a bit of text to describe what the user changed (like the one in Wikipedia).

As for locking , I would include a bit/boolean field for that. After all, a section is either locked or unlocked regardless of what status it is in otherwise. I would lock all top-level sections by default (to ensure that people don't delete whole "articles").

Hope I was clear. Please do ask questions.

P.S. Where are your foreign keys, by the way? Or does MySQL not allow you to define those? And how do you get the forum to format the <pre> tags? Your "code" tag rendered my text completely unreadable, so I just went without any formatting...

Kyareshi P.S. Where are your foreign keys, by the way? Or does MySQL not allow you to define those? And how do you get the forum to format the <pre> tags? Your "code" tag rendered my text completely unreadable, so I just went without any formatting...

I noticed that as well, Could it be that you're using ad-hoc linking for this?

Thank you Kyareshi! I'll need some time to think over everything you wrote before I can respond properly.

At first glance, I am unsure whether `Caption` results in duplicate headers... the goal is at first to let users define what fields of information they wish to add, then later sort those out into default fields that will render on ever page, and extra fields that'll render optionally on any page they were assigned to.

There is something wrong with the Code tags here... for now, it might be best to write REI CHANGE THIS TO PRE at the start and end of the code, since the forum strips HTML from non-admin posts.

As for brobb's follow up....what are foreign keys and why should I use them?

私は日本語を少し読むと書くをできます。あなたも？

Rei At first glance, I am unsure whether `Caption` results in duplicate headers... the goal is at first to let users define what fields of information they wish to add, then later sort those out into default fields that will render on ever page, and extra fields that'll render optionally on any page they were assigned to.

I was under the impression you were making "wikipedia-like" pages for your anime/games, but it seems your goal is something a little different. The history bit still pretty much applies as is, though.

Rei There is something wrong with the Code tags here... for now, it might be best to write REI CHANGE THIS TO PRE at the start and end of the code, since the forum strips HTML from non-admin posts.

Will do.

Rei As for brobb's follow up....what are foreign keys and why should I use them?

Foreign keys (FK) are fields that store primary key (PK) values from other tables. If you're storing the ID for an anime in a field in the character table, that is where you would use a foreign key. Whereas a PK constraint forces every value in a field to be unique, an FK ensures that every value in a field has a corresponding record in the linked table. It helps preserve data integrity.

See: Referential Integrity in MySQL.

Referential integrity isn't a concern. (Developers who make mistakes in this organization are committed to Hara-kiri.) Just kidding. Thanks for the quick lesson on keys. As I see it, their need depends largely on how deletes are handled on the linked table. I can understand there being no harm in using them as a precaution. Once my stubbornness wears off, I'll likely use them.

I did finish a while back making Wikipedia-like pages, using a structure rather similar to what you explained. I do like your solution for subsections, I think I'll keep that.

So from this point, I want to diverge a little from Wiki-like and move towards Multi-lingual Anime Database like.

For example, all anime and game pages must have a Plot Summary. Some anime and game pages might have any of the following taken from random wikipedia anime pages

[list]Director
*Studio
*Licensor
*Network
*Original run
*Episodes
*...etc...[/list]

In this limited context, section titles are likely to be reused. So, there might be some advantage to storing them in a separate table.
Also, I want the option of having Japanese titles on Japanese pages, and Japanese sections on Japanese pages. Plus, a variety of other languages.

The problem I have, is: What is the relation between Sections, Titles and Translations?
Should I do this in one table (plus a history table, following the plan you laid out), or how can I do this with multiple tables linked together?

私は日本語を少し読むと書くをできます。あなたも？

You seriously need to take a pill and get over that stubborn streak of yours, Rei. It's not proper to ask for advice and then reject it on impulse. Especially in the programming world, it is important to accept guidance from the more experienced (and I say this with the utmost modesty).

Trust me when I tell you that foreign key constraints will save you a lot of trouble down the road. Their function is not limited to deletes. In fact, I have never used them to cascade edits like that. They hardly take any extra effort to define, and simply by existing they provide the following crucial benefits:

[list][*]They prevent the input of non-existant IDs. I've seen the mess a database turns into when there are no FKs. A very simple coding mistake can introduce faulty records into your database (this is a lot worse in practice than it sounds now).
[*]They serve to document which fields refer to which tables. Take my word for it; when you have a many cross-linking tables, it becomes a mission to keep track of what fields refer to what. If in doubt, look at the FKs.[/list]
My recommendation was to treat title and sections as similar objects parenting one another, but I'm not sure I understand the way you're trying to use them anymore. As for handling multiple languages, I would just introduce another field into the section table, e.g. "language", and query on that. So when viewing the English page, you would filter on its code (e.g. "WHERE language = 'ENG'), the Japanese page on its own code (e.g. "WHERE language = 'JAP'"), etc. Wouldn't that be enough?

let's see if I can understand what these tables are (maybe this'll give us some clarity on what you want help on and what advice might help most)...

As far as I can tell, the stitle table is used for keeping track of a series title and it's categorization of 'completeness' on the db. The Sections table is used for keeping the bulk of the information on a series. Lastly, the Sectionshistory table is used for backup purposes as well as troubleshooting and has the same information as the sections table.

If I'm right about this, it seems as though the stitles and sections tables can be merged. Of course, the sectionshistory table would have to be modified to match. If that would be too much work then putting an FK on the ID column linking to the Sections ID works.

I see a problem with using this setup when it comes to different languages. Say for instance you have Aria the Natural listed in the Sections table in both English and Japanese, the series would have two different entries and two different ID counters for each language because the ID column is set as the PK and doesn't allow for duplicate values. I see two ways around this: either create different colums in the one item for the different language inputs or create different tables for each extra language that you want added. My first option would create a really big table but would solve it. The second choice seems better IMO because with this you can set the ID column in the different language as an FK linking to the primary language. Another good reason for this is you can use the different language tables as reference to their respective translated pages on the site. ...of course, I'm just thinking within the guidelines of the tables you mentioned, if this is already implemented then you can disregard what I said in the last couple sentences.

[ Edited Feb 25, 2009 ]

Solutions can only be as good as the explanation of the problem... I'm sorry I didn't explain it as well as I should have.

This is what I needed:

/*
	Posts
*/
CREATE TABLE acdbwikiposts (
	id INT unsigned NOT NULL auto_increment,
	/* references */
	pid INT NOT NULL,
	cid INT NOT NULL,
	title INT NOT NULL,
	/* stats */
	uid INT NOT NULL,
	date INT NOT NULL,
	/* flags */
	status TINYINT NOT NULL,
	langid TINYINT NOT NULL,
	/* text */
	content TEXT NOT NULL,
	PRIMARY KEY  (`id`),
	UNIQUE KEY `id` (`id`),
	KEY `id_2` (`id`)
) DEFAULT CHARSET=utf8;
/*
	Titles Assignments
*/
CREATE TABLE acdbwikititlereference (
	id INT unsigned NOT NULL auto_increment,
	/* references */
	postid INT NOT NULL,
	/* flags */
	titletype TINYINT NOT NULL,
	PRIMARY KEY  (`id`),
	UNIQUE KEY `id` (`id`),
	KEY `id_2` (`id`)
) DEFAULT CHARSET=utf8;
/*
	Titles Translations
*/
CREATE TABLE acdbwikititles (
	/* references */
	titleid INT NOT NULL,
	/* stats */
	uid INT NOT NULL,
	date INT NOT NULL,
	/* flags */
	status TINYINT NOT NULL,
	langid TINYINT NOT NULL,
	/* text */
	title TEXT NOT NULL,
	PRIMARY KEY  (`titleid`),
	UNIQUE KEY `titleid` (`titleid`),
	KEY `id_2` (`titleid`)
) DEFAULT CHARSET=utf8;

/* How it all links together */

                / - Japanese
POST -> TITLE ->- - English
                \ - Spanish

brobb, I think this new design fixes the issues with languages.
Sections are large and independent with translations, titles are large and independent with translations, and a 3rd table links them together while providing the option to set title/section pairs as default or extras.

Kyareshi, I could not ask for advice, but you have to admit, that wouldn't be any fun for either of us. I'm sure you reinforced your own knowledge while trying to educate stubborn ol' me. I'm not sure if you've ever met an ideological, philosophical programmer before. What I decide to accept or reject, and what I decide to do or not do is based of my the tenants of my faith (which are ever changing) - not logic or reason. And as for "So when viewing the English page, you would filter on its code (e.g. "WHERE language = 'ENG'), the Japanese page on its own code (e.g. "WHERE language = 'JAP'"), etc. Wouldn't that be enough?" - yes, that'll do. The site sets a langid int, so I'll use that in both tables.

Phase 1 down.... 12 phases to go. *goes back to work*

私は日本語を少し読むと書くをできます。あなたも？

Rei What I decide to accept or reject, and what I decide to do or not do is based of my the tenants of my faith (which are ever changing) - not logic or reason.

Programming = logics.