Need advice on mysql table structure
I think I'm stuck at this problem now and I'm not sure how to proceed.
Here's the task: a php site with different registered users.
Every user can create a directory.
Every directory can be searched by categories, the users create those categories.
Unlimited number of categories, with up to 2 sub-categories each.
I'm explaining this a little complicated, but what it is is - for example - the user needs the following directory:
-USA (LA/ NY/ San Francisco, ...)
- Europe(France/Germany, ...)
- Asia (China/Russia, ..)
-Health (Doctor, nurse, .. )
-Education (Teacher, Student, Lecturer)
Scale: - so on
For every category of those they have let�s say an article. Every article can be placed under different categories, of level2 or 3. For example USA->LA && Health && Education->Lecturer.
We need to search by categories and get the articles as result.
So far I have:
1. Table Categories:
Category_id, user_id, category_label, category_parent
2. Table articles
Article_id, user_id, article_title, article_text
3. Table relations:
Relation_id, article_id, level1_rel, level2_rel, level3_rel
So I know this table structure is far from perfect and I would love to know the better way to do that, but at this point the most important thing is to get this done as soon as possible, as I�m very late with the project.
So if there�s a way just to come up with a query for the search � will be great. Otherwise I�m curious to other ways as well.
Someone needs to be able to search for an article, let�s say USA, San Francisco AND Health/Doctors or Just Education, or just Asia, or not select a category to see all of that user.
(They cannot select just teacher, but can select only Education)
Thank you in advance