I have got thus far with my thinking on database design, but now (I realize I am not a database designer) welcome relevant and usable help for better structure and SELECT-ions that will simplify the task and the subsequent queries to be performed.
I hope I've been as specific as possible:
====================
Background:
A website using MySQL & PHP.
URIs will take the form, in whole or part:
www.foo.com/category/subcategory/articlename.html
(NB: the ".html" extension is not really a physical page, just judicious use of PHP & mod_rewrite)
====================
URI behavior:
www.foo.com
- main home page is displayed
- menus contain only category links
www.foo.com/category/
- home page of category is displayed
- menus contain category and sub-menu of subcategory links within current category
www.foo.com/category/subcategory/
- home page of sub-category is displayed
- list of articles within subcategory is displayed
- menus contain category and sub-menu of sub-category links within current category
www.foo.com/category/subcategory/articlename.html
- article is displayed
- menus contain category and sub-menu of sub-category links within current category
====================
Data to be stored are:
- List of categories
- List of subcategories
- List of articles
- List of authors
- Method of joining these
...all of which contain various data.
====================
TABLES:
====================
* CATEGORY:
- id (integer, auto)
- name (forms the "category" string of the URI)
- title (forms the "title" string when this category is listed in a menu)
- listing (integer - where on the page will this category's link be listed - top/bottom/left, etc.,)
- timestamp
- primary key (not sure which field it should be)
====================
* SUBCATEGORY:
- id (integer, auto)
- name
- title (forms the "title" string when this category is listed in a menu within it's parent category)
- listing (integer - where on the page will this category's link be listed? top/bottom/left, etc.,)
- timestamp
- primary key (not sure which field it should be)
====================
* ARTICLES:
- id (integer, auto)
- name (forms the "articlename" name string of the URI)
- title (forms the <title> and <h1> strings when this is displayed)
- metakeywords
- metadescription
- content (page content - filled with HTML, etc., - the actual page content beyond the <h1> tag)
- class (integer - we will have different levels of users who can view different levels of content; their presence identified by login session or cookie)
- datetoarchive (we have a separate archive section that mimics the site layout exactly, except display only articles that are archived/"expired" from main site)
- timestamp
- live (integer - switches to "1" once admin approved OK to publish)
- featured (integer - articles featured will display in a separate listing within their category and/or subcategory)
- primary key (not sure which field it should be)
====================
AUTHORS:
- id (integer, auto)
- (various name and address data)
- timestamp
- primary key (not sure which field it should be)
====================
JOINING:
(Linking CATEGORY, SUBCATEGORY, ARTICLE and AUTHOR):
(NB: Two categories can have sub-categories with the same name that may or may not be the same sub-category (i.e. same id)
An article only ever has one author
An article can be in more than one sub-category
An article can only ever be in a category only (i.e. not in a sub-cat) as that category's home page)
Only ONE article can ever be the home page of an individual category
Only ONE article can ever be the home page of an individual subcategory
An article that acts as home page of category and/or sub-category can also feature as an article in its own right within a sub-category though normally, home pages will not be listed within sub-categories)
- id (integer, auto)
- cid (Category id field)
- scid (Sub-category id field)
- artid (Article id field)
- authid (Author id field)
- ishomepage (Integer - is this article acting as the home page of the whole website - only one article can ever do this.)
- iscathomepage (Integer - is this article acting as the home page of a category - only one page can ever do this per category.)
- issubcathomepage (Integer - is this article acting as the home page of a subcategory - only one page can ever do this per sub-category.)
- primary key (not sure which field it should be)
====================
Problems I see at this stage:
- As an article can functioning as a home page of the website and/either/or a category or subcategory, could/should ALL the data currently stored in tables "category" and "sub-category in fact be stored within the "articles" database
If you can give examples of SELECT statements pertaining to each case in the section "URI behavior" at the top of this, I would appreciate that, too.
Thank you for your assistance.
David
