Hi,
I'm currently working on the db design for web app. I briefly explained my ideas to someone else who said that they were unsuitable for the purpose but didn't have time to go into detail. Can anyone help me work out the problems with my approach and a better way to go about attacking the problem?

I was planning on having a 'base' table called nodes, which contained common data for every record created in the application: eg id, time created, updated. I would then like to use the id number from this table as both a foreign and primary key in other tables. I would also like to use any attribute which is shared by other models in the application as separate tables which simply contain the base id and the value of that attribute.

For example:

Base table:

id | created_at | updated_at

Page table:

base_id | content

Title Attribute table:

base_id | title

The 'Title Attribute' table could then be used to contain the titles of anything that required a title (if a title is an optional attribute, a record is simply not created in the table). For example, a diary event may require a title, and that could simply be put into the same table and linked to a different model via the foreign base id.

A simple outer join could then be used to retrieve all of the necessary information whenever needed.

It seems logical to me to share attributes between different models rather than repeating them in each table.

I realise this would require more overhead when inserting and performing select queries, but would it not also reduce redundant data, if certain properties are not required? Also, using InnoDB would enable me to remove the entire record by simply deleting the base record and using cascade.

Any advice and tips, opinions and constructive criticism will be greatly appreciated.

Thanks.

Are you going to have several millions of records in your DB? Even if you are, rows of fixed length with title in content in single table will perform much more better than join of two separate tables.