Thread: Problems with this design?
11-07-09, 16:43 #1Registered User
- Join Date
- Nov 2009
Unanswered: Problems with this design?
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.
id | created_at | updated_at
base_id | content
base_id | title
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.
11-07-09, 17:31 #2vaguely human
Originally Posted by magicmooseYou 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.
- Join Date
- Jun 2007
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.
11-10-09, 09:54 #3Registered User
Provided Answers: 5
- Join Date
- Dec 2007
- Richmond, VA
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.