Jump to content


Photo

quick db design confirm


  • Please log in to reply
4 replies to this topic

#1 draxxus

draxxus
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 11 March 2006 - 06:28 AM

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--]user_id[!--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--]category_id[!--colorc--][/span][!--/colorc--] (int, auto-incre)
[!--coloro:#CC0000--][span style=\"color:#CC0000\"][!--/coloro--]user_id[!--colorc--][/span][!--/colorc--]
field
field
field

CONTENT
// the content to be stored inside the categories
[!--coloro:#006600--][span style=\"color:#006600\"][!--/coloro--]content_id[!--colorc--][/span][!--/colorc--] (int, auto-incre)
[!--coloro:#000099--][span style=\"color:#000099\"][!--/coloro--]category_id[!--colorc--][/span][!--/colorc--]
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

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 11 March 2006 - 02:23 PM

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).

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 March 2006 - 05:01 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 draxxus

draxxus
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 17 March 2006 - 05:23 AM

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--]
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
[!--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!

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 March 2006 - 08:13 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users