Query About Database Design Plus Form Implemention

[sonal]

I hope I'm posting in right place.

 

I'm trying to develop a website, where families can make their reading logs. My concept is, one of the parent gets registered to the site. Then he/she can add family members to his/her account. After that, they can make their reading log by adding book entries through the site. Each family member can have their own list of books, under their name. I'm trying to make tables for users and books. What I have come up with till now is as follows:

 

 

Now my confusion is here: A user can register as an individual, where he/she won't have any record in users_details.

A user as a family can have members in users_details tables. A parent can register as a family type user. He can make his own reading log, as well as his family member's reading logs too. I want to add and retrieve, each member's book details. I'm confused about parent_id value. I can keep that value as 0 for individual user, but what about a registering parent? I don't understand how to manipulate this in database and, even in php.

[Antonio Conte]

From what I'm reading, I would suggest two tables for this. First of, I would start with the basic "user" table. That is what you call parent. In my example, id is the important thing. It should be primary key and auto_increment. This is what's identifying all the unique bloggers

 

bloggers (blogger_id, other_blogger_colums)

 

We then make the blog table. Again, the_blog_id is identifing each unique blog. Each unique blog then have an unique blogger associated with it.

 

blogs (the_blog_id, blogger_id*, other_blog_columns)

 

... And a blog_post. Every blog post has an unique identifier in blog_post_id, and what is called a foreign key, a unique identifier in another table, to identify what blog and what blogger the post belongs to.

 

blog_posts (blog_post_id, is_post_of_this_blog_id*, belongs_to_this_blogger*, other_blog_post_columns)

 

At last we make a table where we add bloggers that are allowed to whatch another blogger's personal blog. It works this way: You identify a unique blog by it's ID. You then add an ID of a blogger that is allowed to read a blog.

 

allowed_to_read(the_blog_id, bloggers_allowed_to_read_id)

 

Underlined: primary keys

Asterix (*) : foreign keys

 

------------------------------------------

 

Some test data to make this less abstract:

 

First assume we have three bloggers with an ID, a firstname and a lastname.

 

bloggers (1, "Bobby", "Miles")

bloggers (2, "Johnny", "Cash")

bloggers (3, "John", "Sailor")

 

We then have two blogs (with blog ID, blogger_id and title for the blog) Both belongs to Bobby Miles

 

blogs (1, 1, "This is Bobby Miles' personal blog")

blogs (2, 1, "This is Bobby Miles' second personal blog")

 

We then create three blog posts with a blog_post_id, a blog_id, a blogger_id and the actuall blog_content

 

blog_posts (1, 2, 1, "First blogpost in Bobby Miles' second blog. Just want to say hi to whoever can read this!!!")

blog_posts (2, 1, 1, "First blogpost in Bobby Miles' first blog! Just want to say hi to whoever can read this!!!")

blog_posts (3, 2, 1, "Second blogpost in Bobby Miles' second blog! Just want to say hi to whoever can read this!!!")

 

We then add other bloggers who can read Bobby Miles' two blogs.

 

Now user_id 2 (Johnny Cash) & 3 (John Sailor are allowed to read the FIRST blog (id 1) of Bobby Miles. None of them, are however, allowed to read the SECOND blog if the checks are performed correctly in the php script.

 

allowed_to_read(1, 2)

allowed_to_read(1, 3)

 

------------------------------------------

 

Do you need more help with this, or are you able to figure out the query to check if the user's allowed to read?

 

Just shout out if you're struggling with anything. I'm a bit bussy at the moment, but there's a lot of knowledgeable people here.

[sonal]

Thank you very much, Antonio. I'll figure out how I can go further. I'll be back with my finished page, and if not, then more questions.

[Antonio Conte]

Good luck. Just ask if you're wondering about anything. It's not that easy to grasp in a heart beat. I've helped a lot of people with similar problems. (Sub-menues, and all other kinds of three-structures do really share the same solution.)

 

You can think of this like a multi-dimensional array if that helps you out. Larry writes about this in PHP 5 Advanced (I think) where he builds a PHP and database system for a todo-list. That is really pretty much the same as you do here.

 

Hope that helps.