Enhancement: DB design improvements

[GoogleCodeExporter]

What steps will reproduce the problem?
1.
2.
3.

What is the expected output? What do you see instead?

What version of the product are you using? On what operating system?

Please provide any additional information below.

Please download MySQL Workbench 5.2.x to view the .mwb file (MySQL
Workbench 5.1.x is not enough):

New design proposal
----------------------------

ContentNodes:

The key point here is the composite primary key
consisting of the page's ID (an identifying
relationship) and the type the node represents
(which should be an ENUM). The composite primary
key ensures no duplicates will occurr.

The foreign key allows setting ON DELETE CASCADE
and ON UPDATE CASCADE if page IDs get updated or
deleted. There's no need to program this in PHP.

This design also circumvents the parent ID naming
problem seen in the old content_nodes. Using
'page_<id>' columns is not a good idea. Furthermore,
you can yield the page ID without a join.

Pages:

is_home_page is redundant. Set the "home page"
parent_id = NULL, for 404, Site Offline, etc. you can
still place them anywhere in the page hierarchy, but it
would probably be best to set their parent_id to the
"home page". Setting the parent_id = NULL should be for
the absolute root only. After all, the show_on_menu flag
represents the visibility of 404, Site Offline, etc.

Original issue reported on code.google.com by kwut...@web.de on 2 Mar 2010 at 12:41

Attachments:

• digitalus-nextgen.zip

[GoogleCodeExporter]

Original comment by lowtower1974@gmail.com on 3 Mar 2010 at 8:10

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

Hello kwutzke,

as far as I know, CASCADE only works on MySQL versions beginning from v.5.1.
I guess that most users/hosters have versions prior to 5.1 installed (me 
included).

Do You agree?
Cheers,
LT.

Original comment by lowtower1974@gmail.com on 3 Mar 2010 at 8:59

[GoogleCodeExporter]

Not really:

http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html

"CASCADE: Delete or update the row from the parent table and automatically 
delete or
update the matching rows in the child table. ON DELETE CASCADE is supported 
starting
from MySQL 3.23.50 and ON UPDATE CASCADE is supported starting from 4.0.8. 
Between
two tables, you should not define several ON UPDATE CASCADE  clauses that act 
on the
same column in the parent table or in the child table."

Original comment by kwut...@web.de on 3 Mar 2010 at 3:23

[GoogleCodeExporter]

i found this:
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html

Maybe, it's only meant in connection with "DROP TABLE"

"RESTRICT and CASCADE are allowed to make porting easier. In MySQL 5.0, they do 
nothing."

Original comment by lowtower1974@gmail.com on 3 Mar 2010 at 3:31

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Hello Karsten,

just for my better understanding!
Let's say, we add a new column `parent_type` to `content_nodes` of type 
ENUM('pages',
'users').
In `parent_id`, we only store the id of the parent of type `parent_type` (either
'pages' or 'users').

How can we set a foreign key for `parent_id` if the foreign table is not kown - 
might
be `users`, might be `pages`.

Cheers,
LT.

Original comment by lowtower1974@gmail.com on 5 Mar 2010 at 10:54

[GoogleCodeExporter]

Define a common super table for users and pages (inheritance). I don't know 
what to
call it, but it would be much like an abstract class in OO. The sub tables 
pages and
users would probably define an identifying relationship/foreign key to the super
table, so the sub tables would share the same pool of generated IDs.

You then have content_nodes set a foreign key to the abstract super table. 
Sure, this
design slightly more complicated but doesn't need any workarounds.

Original comment by kwut...@web.de on 8 Mar 2010 at 12:09

[GoogleCodeExporter]

On MySQL: I'm not 100% sure, but I suspect the comment you picked up with 
CASCADE and
RESTRICT only applies to when the whole table is dropped, so the CASCADE rule 
isn't
executed. Often cascades go over several levels/tables, so dropping a 
higher-level
table would (inadvertently) empty all tables that have CASCADE foreign keys on 
them.

Original comment by kwut...@web.de on 8 Mar 2010 at 12:11

[GoogleCodeExporter]

I don't think this is a good idea, because pages and users have nothing in 
common
except the id.

I'd rather delete the user's note feature (or store the note elsewhere) and 
make the
content_node solely dependent on the table pages.

Original comment by lowtower1974@gmail.com on 8 Mar 2010 at 7:32

[GoogleCodeExporter]

You were asking for a solution on how to set a foreign key if the table isn't 
known,
right?

I didn't go for semantic redesign here. Surely, pages and users don't have much 
in
common, but then there's a conceptual misdesign that content_nodes can be 
assigned to
users and pages using the same table.

Why are page and user properties not put into two distinct tables, so that the 
FK is
unambiguously linked to either pages or users? I mean, page and user properties 
have
nothing in common as pages and users themselves, too, right, so why cram them 
into a
common table?

Original comment by kwut...@web.de on 8 Mar 2010 at 8:58

[GoogleCodeExporter]

A complete tables's redesign has taken place and will be part of the forthcoming
version version 1.10

Original comment by lowtower1974@gmail.com on 6 Apr 2010 at 9:38

• Changed state: Fixed