Database Model

[Migrated From Jforum.net]

Hi Guys!

I'm taking a look around the JForum's database model, understanding the tables, fields, ...

I think it could be interesting to comment the "columns", because to the beginners it would be easier to understand each objective, and so on.

Maybe it be stupid, so please forgive me since now, ok? ops:

In the MySQL documentation it's a few hard to understand this:

Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT( 4 )). This optional display width specification is used to left-pad the display of values having a width less than the width specified for the column.

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values having a width exceeding that specified for the column.

So, why INT( 4 ) sometimes, MEDIUMINT( 8 ) another time, VARCHAR( 8 ), VARCHAR( 255 ), ...

A practice example:

Table: jforum_users

column: user_active - TINYINT( 1 )
column: user_level - TINYINT( 4 )

:?: What is the practice difference between the ( 1 ) and ( 4 ) after TINYINT?

:?: Ah, I think than the user_active column is used to inform if the user has used the forum since some days ago... but what is the user_level mean, for example?

Don't you agree that comments would let it easier to lammers (like me ;- )?

One more idea: there are some softwares to database modelling, like Erwin and DBDesigner.

:?: Wouldn't be interesting to model the JForum's database in a tool like that?

I could comment and/or model it, but let's know what you think about...
[originally posted on jforum.net by leandro_salvador]

[Migrated From Jforum.net]

leandro_salvador wrote:
So, why INT( 4 ) sometimes, MEDIUMINT( 8 ) another time, VARCHAR( 8 ), VARCHAR( 255 ), ...

I can talk about the choice between MEDIUMINT and INT, but the the difference in VARCHAR is there, because the DB has to actually reserve space for 8 chars or 255 chars. The difference between INT and MEDIUMINT is probably the range of the number and that affects the amount of bytes used to store the value physically.

leandro_salvador wrote:
:?: What is the practice difference between the ( 1 ) and ( 4 ) after TINYINT?

.- On one side, it seems to be used by MySQL to specify tools how many "digits" the value is expected to have, so for example a textfield created automatically for those fields, by a tool like MyPHPAdmin, can have the appropriate width.
.- On the other side, it can still be considered some kind of "documentation" so when you port the script to other database that has strict digits compliace, you have not lost that information.

So, documentation more than anything else
[originally posted on jforum.net by GreenEyed]