quick db design confirm

[draxxus]

Just wanted to make sure my db is properly setup before I go off on my application.

It'll be a sort of user cms system that provides users with their own web pages with their own content, etc. (the content/layout is established by me though, they're just going to be filling in fields like a profile or something) I guess it works simularly to a blog.

The colors show the rows that are dependant on eachother.

ADMINISTRATION TABLE
// this table is for the users to manage their website
[!--coloro:#CC0000--][span style=\"color:#CC0000\"][!--/coloro--][b]user_id[/b][!--colorc--][/span][!--/colorc--] (int, auto-incre)
field
field
field
field

CONTENT CATEGORIES
// this table allows them to make a category in which content will be put
[!--coloro:#000099--][span style=\"color:#000099\"][!--/coloro--][b]category_id[/b][!--colorc--][/span][!--/colorc--] (int, auto-incre)
[!--coloro:#CC0000--][span style=\"color:#CC0000\"][!--/coloro--][b]user_id[/b][!--colorc--][/span][!--/colorc--]
field
field
field

CONTENT
// the content to be stored inside the categories
[!--coloro:#006600--][span style=\"color:#006600\"][!--/coloro--][b]content_id[/b][!--colorc--][/span][!--/colorc--] (int, auto-incre)
[b][!--coloro:#000099--][span style=\"color:#000099\"][!--/coloro--]category_id[!--colorc--][/span][!--/colorc--][/b]
field
field
field

The actual database is more detailed with many more tables but they're all setup basically this way, stacked on one another. I'm pretty sure this is how it should be done, but it sure seems that if I want to delete something at the bottom of the tree (in this case the CONTENT table) than its quite a bit of work to acquire all the info to simply find the specific row to delete. By that I mean I have to select the user's info from the top and keep going down the list selecting the keys just to delete a row at the end. It seems more efficient to just put user_id in every table so if I wanted to delete something I simply find it in the table based on the user_id.

I appreciate any thoughts you guys could provide.

draxxus

[wickning1]

Looks right to me, as much as you posted. Note that you can do joins in a delete statement so you don't actually have to make a lot of queries. But yeah, it can get complex quickly. I always make sure to have a current reference diagram on hand until I really know the system (and it stops changing rapidly).

[fenway]

Seems OK -- you should probably have your UID as UNSIGNED, so you get twice the mileage. Yes, you can run a multi-table delete (!), but I prefer using a typical SELECT statement (with JOINS) to get back the desired uid, and then write an easy DELETE statement. Of course, nothing stops you from doing this as a subquery to the DELETE either. I just find the mulit-table DELETE syntax a little counter-intuitive.

[draxxus]

Is there a more efficient way to delete stuff than having a million DELETE statements inside of a million other SELECT statements? I see that it works, but the problem is when it comes to a place where there is nothing to delete (but something needs to be later down the path) it just stops the whole deleting process. I'll try to explain it as best I can.

I have a database for schools. I have three tables for schools, teachers, and assignments. The assignments relate the the teachers table, and the teachers table relates to the schools table. The objective is to delete a school (and everything that corresponds with that school, i.e. the teacher) from the admin panel. Currently, my deleting process looks like this -
[!--coloro:#990000--][span style=\"color:#990000\"][!--/coloro--][b]
SELECT statement to select the school information
while loop for the rows as results
{
SELECT statement to select all teacher information based on the school's ID
while loop for the rows as results
{
DELETE statement to delete the assignments based on the teacher's ID
}
DELETE statement to delete all the teachers in that school
}
DELETE statement to delete the school itself[/b][!--colorc--][/span][!--/colorc--]

The problem is if I try to delete a school that has no teachers in it, it'll stop at the DELETE assignments (because without any teachers, there can be no assignments) and won't continue on. How would I enable it to continue on even if there are no teachers?


Thanks for the help!

[fenway]

Well, if you have InnoDB tables, you can have the refernetial integrity enforce this automatically. Also, you can simply check to see if you have any records to delete first based no the previous UID lookup.