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:
Page table:
Title Attribute table:
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.

You haven't really told us what your system does or what it is you're trying to model so it's impossible giving you solid examples of why your design won't work. Nevertheless I agree with your friend who said the design is poor.

Let's say we're displaying a page on a special type of widget you're selling - how do I know which tables to grab the data from? It's slower as well as rather than pulling all the data from one table I now have to pull from 10 tables. Just updating or inserting the details on a particular widget is a pain too - how many separate tables will I need to alter?

Can you give any examples of how your design might benefit? A better way of designing your database is just to use the standard practise of normalisation which has worked well for decades.

You don't mention if these subordinate tables are a one to one relationship or one to many. If many then you are on the right track, if it is one to one then the data should be in one table. If many, then you need to add to your pks for those tables.

Dave