Moderators for Questions and vice-versa (theory needed)

[alexus]

Hi all!

I need help with concept:

I have like 30 categories of questions stored in DB, in theory I have to assign and moderator to each category. Well that is no problem, but as turned out, what if I need more than one category per moderator (or reverse). First I thought to add extra field that will used for second something? Also I would like to assign moderators rights in their profiles, not to each question separately?

Ok, so maybe somebody have some ideas? If not I?ll assign moderators to each q. and ?

[lig]

How about a table for moderators, one for catagories, and a linking table Rights

Mod = individual moderators data
Cat = the individual catagories

Rights =
Catagory (Foreign Key)
Moderator (Foriegn Key)
Moderators Rights (I would codes for the various ones and use the comments in the table create to define them)
Primary Key (Cat, Mod)

Is this like something you had in mind?

[alexus]

what do you mean “forieng key”? Is MySQL an a relational DB? (As far as I know NO)
I’m not sure what you are saying but let me try to explain what I have now:

table called Questions that contain Question, Answer and Category
table called Users:User name, Password, Access Level (1- User, 2-Moderator, 3-Admin) [I expected to assign categories here)

---

Idea #1
(One Moderator Diferent Categories)
In this case I’m assigning moderator (his user name) to each question [when question submited I check with DB to whom to assign question, and then saving record with q] then I just SELECT * questions WHERE moderator=‘John Doe’;

---

Idea #2
I some how store information about which cats. to select in Users table
then I just select where cats are needed
– I’m still not clear about this one because it seams that something measing…

[lig]

Actually MySQL is one of the bigger (and arguably better) relational databases. It has most of the same capabilities of Oracle and Sybase (but for free) and is getting more all the time. I am sorry I thought you had more knowledge on databases and their design. I apologize.

OK a foreign key is a link/connection between tables. Here is a link to some tutorials about database design ( http://www.geekgirls.com/menu_databases.htm ). Pay particular attention to the ones on design (right side). There is where you will find what I am talking about.

I would suggest you read those tutorials (Asking questions about what you are confused on) and then coming back and looking at my answer. If it still doesn’t make sense let me know and I will try to clarify.

If you wish to follow your design we can do that also, but you might be restricting yourself for later growth.

[alexus]

That link explayning the theory on Relational DBs, I know that…
I’m using MySQL Controll Center (MCC) and there is no tools for creating links (comparing to Oracle or MSDE)

[Bane]

The fact that there is no support for foreign keys does not mean the database is not relational. Those relations can be simulated by the queries. The only difference is that relationship integrity is not handled by the SGDB. I have to admit MySQL is far from being the best database server around, but it suits most needs and it’s blazing fast.

By the way, MySQL has support for foreign keys when using InnoDB tables. Just do a simple search in the MySQL doc.

[alexus]

isn’t InnoDB is an extra (add-on) part? and it requires Hosting Provider to configure and instal it seperatly?

[alexus]

well any way, this in not about DB,
If I would have simulated linkage how would it solve the problem, unless
I would use somethin like SELECT * rights WHERE moderator=’$x’

and table rights will not have index keys so it would allow add moderator/category many times…

Sounds good for me (for now), but lets see if it will work…

Thanks for help, and comment me if I’m doing something wrong

[alexus]

Would it be most eficient way?:
[php]

<? include('login.php'); // Data base connection vars include('db_conn.php'); // Data base connection vars $db = mysql_connect($db_server, $db_user, $db_pass); mysql_select_db($db_name, $db); $sql = "select * from faq_experts WHERE expert='$u_name'"; $result = mysql_query($sql); $rows = mysql_num_rows($result); while ($row_news = mysql_fetch_array($result)) { $sql = "select * from faq WHERE subject='$row_news[cat]'"; $result = mysql_query($sql); $rows = mysql_num_rows($result); while ($row_news = mysql_fetch_array($result)) { echo $row_news['question']."
"; } } ?>

[/php][/php]

[lig]

how about

SELECT * FROM faq_experts, faq WHERE faq_experts.expert=’$u_name’ AND
faq.subject=‘faq_experts.cat’;

I beleive that should return everything you want for both in one SELECT.

[alexus]

That rerurns me a blank page… no errore hough
probably because AND returns 0

Mean while when I use my SELECT it returns me a 5 lines of [‘question’] …