[RESOLVED] Advice Requested: Table Structure

Rodney_H_

Hi:

I am about to embark on a Resource Center for a website, so they can easily manage assets in their company internally, and things that their customers may need.

Can you look at my current DB design and let me know if you have any suggestions/recommendations for me:

Resources_Categories
id (primary key)
category VARCHAR(40)
level SMALLINT(3)
order SMALLINT(3)

Resources_Types
id (primary key)
type VARCHAR(25)

Resources
id (primary key)
name VARCHAR(50)
description (full text)
type (foreign key)
access enum('private','public')
author_id INT(6)
date_added DATETIME

Resources_to_Categories
id (primary key)
resource_id (foreign key)
category_id (foreign key)

Thanks.

tomhath

Two comments:

1) Instead of "type" as a foreign key in Resources, you should use something like type_id and link to the id column in Resources_Types (because "id" is the primary key in Resources_Types).

2) Can a Resource be in more than one Category? If not, just put the link in Resources. If it is a one-to-many relationship the Resources_to_Categories table is okay, although I wouldn't bother with an "id" column on that table (the pair of links should be the primary key); unless you're using a framework that requires an "id" column like Ruby on Rails.

Roger_Ramjet

Just a comment on naming conventions :

Naming the primary key 'id' in every table will lead to confusion at some point. Maybe not for you, now, but if someone else has to maintain your code they are going to end up asking "Which table id is it?".

Best to name each primary key for the table, abbreviated if you prefer: Resources table = r_id, Resources_to_Categories = rc_id, etc. You can then also name foreign keys as the same, making the relationship explicit and obvious.

One advantage of this is that you can use the short-hand form in JOINS: r INNER JOIN rc USING(r_id). The obvious advantage of this is that it is less typing, but the real advantage comes in the resultset as SELECT * will still only return the column once.

Rodney_H_

tomhath wrote:
1) Instead of "type" as a foreign key in Resources, you should use something like type_id and link to the id column in Resources_Types (because "id" is the primary key in Resources_Types).

yes. Good point. That is actually what I was trying to depict, but I have never "drawn" a DB schematic before, LOL!!

tomhath wrote:
2) Can a Resource be in more than one Category? If not, just put the link in Resources. If it is a one-to-many relationship the Resources_to_Categories table is okay, although I wouldn't bother with an "id" column on that table (the pair of links should be the primary key); unless you're using a framework that requires an "id" column like Ruby on Rails.

It is a one-to-many relationship.

Further, there will be "sub-categories" as well, so not only will a resource be within a category, but it may be in a sub-category as well.

It may even be in multiple categories, as inelegant as that thought is, it might be possible.

Thanks for your help.

I appreciate it.

Rodney_H_

Roger Ramjet wrote:
Naming the primary key 'id' in every table will lead to confusion at some point. Maybe not for you, now, but if someone else has to maintain your code they are going to end up asking "Which table id is it?".

Is it silly to use the naming convention as is, but to use ALIAS properties/column names in my queries??

Roger Ramjet wrote:
...but the real advantage comes in the resultset as SELECT * will still only return the column once.

R.R., can you elaborate on that? I am either thick-as-a-brick tonight, or need a little guidance in your meaning.

Gracias, muchacho...

Roger_Ramjet

Rodney H. wrote:
Is it silly to use the naming convention as is, but to use ALIAS properties/column names in my queries??

Waste of time and lots of extra typing.

Always code on the basis that someone else will have to work with it one day. By naming primary key id columns for the table, anyone can see immediately which table is refered to. By also naming related foreign keys the same then it is obvious what the relationship is without having to examine the db schema - which probably won't exist in any case.

table Main
main_id
type_id
cat_id
branch_id
etc
etc

table Types
type_id
etc

table Cats
cat_id
etc

table Branches
branch_id
etc

See, the whole thing becomes obvious.

As to the second point: it comes from my background in client/server apps.
Rather than create a query naming specific fields, one just does SELECT * and passes all the data to the 'client'. It makes it easier in the long run when you add columns to the tables (which you will) and/or the client GUI needs changing, you don't have to go back and change the query as well.
Using 'USING (cat_id)' in the JOIN clause means that you don't get the cat_id column returned twice, which you would if you used 'ON (main.cat_id=cats.id)' or even 'ON(main.cat_id=cats.cat_id)'. Less data to send and less confusion as to which id column is which.

Rodney_H_

Cool. Thanks for taking the time to explain.

I am glad I asked before I started coding, too.

Kind regards, RR.