Database structure for numerical category organization

Ryodox

I am making a website that will be a simple database of articles and facts, organized into numeric categories from 000-999 (each increment of 100 would be a new category, each increment of 10 would be a sub-category) and each fact would have an individual number, like 205.4, and so forth. Each article would be coupled with a source, consisting of multiple fields like book, author, date, web link, etc.

My question here is, basically, how many tables should I have and of what? I'm considering: one table for categories, one for subcategories, one for individual facts, and one for sources, but I'm not sure what fields I need to have or how I would be able to link them together in a coherent fashion, or even if this is the best approach. Thoughts?

bradgrafelman

If you haven't already, I would highly recommend you read this article regarding database normalization.

Ryodox

Ah, thanks for that. So I'm considering using the following table setup:

categories
cat_id // primary key
cat_name
cat_min // minimum number of category range
cat_max // maximum number of category range

subcategories
subcat_id // primary key
cat_id // foreign key to categories
subcat_name // name of subcategory
subcat_min // minimum number of subcategory range
subcat_max // maximum number of subcategory range

facts
fact_id // primary key
subcat_id // foreign key to subcategories
src_id // foreign key to sources
fact_content // text content of fact

sources
src_id // primary key
src_content // text content of source

Could I do better with this?

NogDog

Is there a particular functional requirement that is locking you into the 000-999 numbering scheme? Also, is there any possibility that you'll ever have a 3rd or lower level of category?

Ryodox

NogDog wrote:
Is there a particular functional requirement that is locking you into the 000-999 numbering scheme? Also, is there any possibility that you'll ever have a 3rd or lower level of category?

Well, I've actually considered using numbers 00-99, so there probably won't be a third lower organizational level. I want to use the numerical system since it's easy to reference, as each range of numbers is an individual category/subcategory, and each entry has an individual number. It works similar in principle to a thesaurus.

NogDog

I was just trying to determine what the significance was (if any) to the numbers, (such as with the Dewey Decimal System) and whether or not you were sure there would be only two levels and that articles/facts would always be under a sub-category.

Also, is there any possibility you would ever want a fact/article to relate to more than one category and/or sub-category?

I'm just trying to make sure you don't design things too narrowly, and then find out down the line somewhere that you need to change your database schema or go through programming contortions to make things work. 🙂

For instance, you could have just one categories table, and include a parent_cat_id field that would establish that category's parent, with top-level categories pointing to a zero or null value. This would allow for essentially any number of category levels.

And if articles might logically relate to more than one category, then you could have a separate article_to_category relation table, allowing for many-to-many relationships.

Just some food for thought. Hopefully it makes a little sense, as it's past my bedtime, so I'm out of here for now. 🙂

Ryodox

I don't believe I'll have facts relating to more than one category, no. I will use your suggestion, however, and keep categories and subcategories in one table.

Roger_Ramjet

It sounds like you are re-creating the Dewy Decimal System, or Wikipedia, used by librarians to classify books etc according to their subject matter and content.

Ryodox

Yes, what I have in mind is very similar in nature to the Dewey Decimal System. 🙂