DC - Database structure

Haris · 12-10-2007, 10:55 AM

Project DC is my secret project which will be revealed to some people with the database structure.

I want to get feedback on the database structure which I have planned. I would also like to ask what limits should I use for the common columns such as username column, password column, title column, article column and more..

http://docs.google.com/Doc?id=dz2vtgb_4p5ck2732

macov · 12-10-2007, 11:41 AM

It is always good to have a business requirements. Why are you keeping ip address in the user column? IP will be changing everytime an user will be logging. The salt will be the same for each user? If yes, why are you keeping it in the user table?

Haris · 12-10-2007, 02:21 PM

Quote:

Originally Posted by macov

It is always good to have a business requirements. Why are you keeping ip address in the user column? IP will be changing everytime an user will be logging. The salt will be the same for each user? If yes, why are you keeping it in the user table?

No, the salt will be generated dynamically.

IP address idea seems a bit vague, at the moment. I thought, it could be used for banning ip range of spammers in future.

macov · 12-10-2007, 04:37 PM

Make yourself familiar with the idea of session.

IP changes everytime user log in, therefore you will need a table 'sesssions' (id, users_id, ip, etc..) to manage their activities.

A table Tutorials has user_id, is that means that this user created a tutorial or a user is taking this tutorial?

Haris · 12-10-2007, 06:04 PM

Updated, thanks.

bdm · 12-11-2007, 02:01 AM

Is it just me, or is that database not normalized?

If I'm thinking this right, from the users table, rank should be in its own lookup table. The best sign that your tables aren't normalized is when you start putting 1-2 at the end of your column names.

From the tutorials table, status should probably also go in its own lookup table. Also, the status field for both recipes and comments table should go in its own lookup table.

Also, it probably wouldn't be a bad idea to draw out an ERD (Entity Relationship Diagram) to visually see the relationship between each table (if any).

You could also most likely get away with using a tinyint as your category id unless you are expecting 127+ categories (http://dev.mysql.com/doc/refman/5.0/...ric-types.html)

Can someone clarify anything I've been saying? I'm no MySQL guru by any means.

@macov: Why do you think the users IP will change everything they log in?

Haris · 12-11-2007, 03:22 AM

I have seen lots of examples of developers using rank in the same table of users.

Status will simply tell if the comment,article or recipe has to be hidden or shown. A table for just its status might be overkill.

I might have more than 127 categories overtime but initially, around 20.

Included more stuff to the document.

macov · 12-11-2007, 09:46 AM

It is really hard to comment until it will be in the graphic form and relationships between entities will be presented. I suggest using DBDesigner 4

Haris · 12-11-2007, 10:03 AM

Ok, I'll get back with it. :)

bdm · 12-11-2007, 12:07 PM

The reason I suggested using lookup tables is, what if you decide to add another rank or status? What if you wish to change the name of a rank or status?

Here are a few advantages of using a lookup table:
- No data duplication
- You can easily fetch all ranks and status' with a simple query
- You can easily add or edit the ranks or status' without messing in the code

And actually, if you use an 'unsigned tinyint', you can go up all the way to 255.

Code:

Through the process of database normalization we bring our schema's tables into conformance with progressive normal forms. As a result our tables each represent a single entity (a book, an author, a subject, etc) and we benefit from decreased redundancy, fewer anomalies and improved efficiency.

- Mike Hillyer

Here's an interesting article you may find interesting. http://dev.mysql.com/tech-resources/...alization.html

Haris · 12-11-2007, 04:13 PM

I would appreciate if Wildhoney or any PHP guru could give his opinion.

bdm · 12-11-2007, 04:41 PM

You're model seems incomplete. You have a users table, but no username, fullname, password, etc.

Also, you'll want a one-to-many relationship between your tutorials and comments table. With the way you have things set up, it seems like each tutorial can only have one comment. I would probably make a bridge table between the tutorials and comments table.

Tutorials
- id (PK)
- title
|
|
Tutorials_Comments_Bridge
tutorial_id
comment_id
|
|
Comments
- id (PK)
- user_id
- text

Same would go for your recipes table.

And I'm getting confused with the tutorials table. You have category_ID and tutorial_category_ID. Same with your comments table. I also don't think the category_ID is needed in your comments table since you would be getting the category name from the tutorials table when you join the two.

That way, you could have as many comments per tutorial as you wish. All you would have to do is join both table and easily get all comments for tutorial_id n.

Although, I would like to get a second opinion.

Thanks.

Haris · 12-12-2007, 08:45 AM

Now, take a look.

Haris · 12-12-2007, 10:27 AM

I am NOT really well-versed with database designs. I would be glad if someone could make a tutorial-like explanation how my database should be really helpful.

bdm · 12-12-2007, 12:10 PM

I don't see any relationships. :(

In order to normalize your database to the third normal form (3NF), ranks should go into a separate table. And no, this isn't overkill and it will eliminate data duplication for your ranks. Not to mention that having a lookup table is very useful.

Take a look at the following link and look how the PaymentType3NF table is and how it is linked to the OrderPayment3NF table.

http://www.agiledata.org/images/dm101Order3NF.gif

Haris · 12-12-2007, 06:43 PM

Tried 10 times with DBdesigner to create a proper user model that has its status, rank and 'tutorials completed' table normalized. No such luck.

.

That's the best I could come up with.

Users table description:

Every user can have only one status.
Every user can have only one rank
A user can have many tutorials completed

Linked table description:

Many users can have same rank. writer, editor, member
Many users can have same status. 0 banned - 1 active
Many users can complete same tutorials.

I was soo confused that I could only develop model between users, ranks and status only.

http://www.imgsync.com/data/img/4859350db-users.png

bdm · 12-12-2007, 10:46 PM

Nice, making some progress.

Unfortunately, I have no idea how to make relationships in DBDesigner as I don't use it. But as far as I know, MySQL does not handle relationships. But it helps to visually see the business rules. :\

bdm · 12-13-2007, 01:26 PM

Also, I don't see the users' completed tutorials/

And what is 'adsensePUBID' used for?

Haris · 12-13-2007, 03:50 PM

Quote:

Originally Posted by bdm

Also, I don't see the users' completed tutorials/

And what is 'adsensePUBID' used for?

That one is really hard to make.

The adsensePUBID will store the user, adsense publisher ID.

I want to reduce the tutorials,recipes and articles table into one table called "content".

The tutorials doesn't need ingredients and method, the articles doesn't need ingredients and method however the recipes require ingredients and method.