AID - To distinguish articles
Title - Title to appear on the top of every page
AuthorID - Links to Authors table which stores profiles etc.
Published - Date article was first put on the Web.
Updated - Last date in which article was updated.
ArticleText - To storep text on any specific article.
Authors..
.........
AuthorID - Assigns each article an ID
Profile - A brief about the author
ArticleLookup..
...............
AID - Links an article with ...
CID - ... a category[/code]
Do you think this database layout is good? Will it will have any problems with that "anomaly" stuff? Thanks in advance for your feed back and help!
I just forgot to think about one thing in this layout: the subcategories. How would I go about making subcategories in just some of the categories (so when you click on some categories it would show up all the articles while others would link to the subcategories)? You can read my previous post here. Again, thanks for your help (and sorry Eric that I didn't understand your explanation).
I don't really get it. I thought most databases needed a lookup table. Won't it be quicker if I have a separate lookup table instead of having that data with the articles?
As for the AuthorID the purpose of that is so each author has one record and if their record is changed all articles will have their new profile. What did you mean by deleting it from one of the tables?
Probably everything I said in this post is useless. I'm dumb at this stuff anyway...
Firstly you should keep the ArticleLookup table. If you used a TopicNumber column in Articles instead you would not be able to have a book listed in multiple categories
The lookup table will also allow you to create subcategories. The first thing you should do is assign a number to each category and also to each subcategory. Then, for example, book X could be in category 6 and in subcategories 20 and 23. The database itself will not 'know' the relations between categories and subcategories - 20 could be a subcategory of category 4 and 23 could be a subcategory of category 6. You will need to keep a record of this structure somewhere else. You can then create a different record in ArticleLookup for each category/subcategory the book appears in.
I would split the Authors table up a bit more. How about having fields such as Name, Email, ICQ, and WebSite? This will keep your data much more ordered than it would be with just a Profile field.
------------------
Daniel Irvine F-Free - Free Stuff and Freebies!
I agree with d3v - one of the AuthorID columns should be renamed...when you query the database from the webpage it will get confussing if there are two columns with the same name. So rename the field in one of the tables.
I hope that cleared up what d3v said.
It was simpler than I thought! You mean it should look like this?
<BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>TABLES..
........
........
Articles..
..........
AID - To distinguish articles
Title - Title to appear on the top of every page
AuthorID - Links to Authors table which stores profiles etc.
Published - Date article was first put on the Web.
Updated - Last date in which article was updated.
ArticleText - To storep text on any specific article.
Authors..
.........
ID - Assigns each article an ID
Name - Author's name
Email - Author's email
ICQ - Author's ICQ number
Website - Author's website(s)
Profile - A brief about the author
ArticleLookup..
...............
AID - Links an article with ...
CID - ... a category[/code]
Thanks for your help so far! Are there still any problems with this layout? Do you think it is an "expandable" layout for a database?
[This message has been edited by pedro_gb (edited July 23, 2000).]
To store the category-subcategory structure, you'll need another lookup table. The two columns will store "parent-child" pairs of category ID's. Categories not appearing as "parents" in this table will be your "main categories". Writing the SQL query to list the "main categories" (i.e. listing all categories that do not appear in the "parents" column of the category-subcategory lookup table) will be the hardest part, so here's the query for you. :-)
SELECT Categories.CID, Categories.Name, COUNT(CatLookup.Parent) AS count
FROM Categories LEFT JOIN CatLookup
ON Categories.CID=CatLookup.Parent
GROUP BY Categories.CID
HAVING count=0;
Be sure to read Part 9 of my series to find out how this works! :-)
You can place an article in multiple categories simply by adding another record in your ArticleLookup table. For example, say article 12 was in both categories 2 and 3. Then you would just need to have the following records:
Also, I would have to agree with temis on the naming issue. Names (especially key fields) should always be kept constant. That was one of the things I was taught about database design anyway Also even though you need to write more code, the code will be more specific and it will therefore be easier for other people to understand when they read it.
------------------
Daniel Irvine F-Free - Free Stuff and Freebies!
Okay... I will have to read part nine then... But now two other problems came in this design. I want some articles to appear in more than one category. Fine. That is not the problem. But I want to have something near the top of the page telling the visitor where he is (like SitePoint has). That is one problem. The second one is: what will happen if the article is in more than one category? Thanks in advance for your help (again)
quinn, i disagree with you. Why does the authorID need to be renamed? He's storing an article posted by an Author identified by an authorID (foreign key), then looks up the author details in the authors lookup table, where AuthorID is a primary key. If you rename it will create confusion.
Category lookup table should solve your problem about telling the visitor where s/he is. You can pass CID as a variable in the URL
(<a href="pageName.***?CID=***"> or similar)
This maybe a stupid question but here it goes: is there any way in achieving that effect without having to pass the variable in front of the visitor's eyes but with them still being able to bookmark it?
The way SitePoint does this is to just look in the database and get a list of all categories that the article you are requesting belongs to, then just picks the first in the list to display. So an article appearing in multiple categories will only ever display the "first" category it belongs to at the top of its article page. Since categories aren't a huge element in the way most users navigate our site, this is quite acceptable. To actually track which category the user selected the article from, however, we would indeed need to pass the category ID.
/font><HR><pre>TABLES..
Bookmarks