Database design

[enkidu72]

Hello all ,

I have what is (probably) a very stupid question ...

 

I need to create tables for a db but I'm a bit confused and I hope someone could help ...

 

I have a table members  containing details about users:

describe members ;

+------------+-------------+------+-----+---------+----------------+

| Field      | Type        | Null | Key | Default | Extra          |

+------------+-------------+------+-----+---------+----------------+

| id                | int(4)          | NO  | PRI |      | auto_increment |

| username  | varchar(65) | NO  |    |        |                |

| password  | varchar(65) | NO  |    |        |                |

| group        | int(4)          | NO  |    |        |                |

| permission | int(4)          | NO  |    |        |                |

 

 

And now I have to create a group table .

User and group are unique , but I need a user to belong to more groups ...

 

Which is the best way to do it ?

 

Thx in advance

 

David

 

[kickstart]

Hi

 

Take Group out of the users table. Have a table containing some ID, the users ID and the group ID, and use this to link the user to the groups they are in.

 

All the best

 

Keith

[enkidu72]

Many thx 

kickstart

[markwillis82]

slightly off topic - kickstart... are you also on BCF? I used to be a member (not sure if my account is still up) it was "drummer"

[enkidu72]

Is that right ? It seems to work but seems "pervert" to me

 

mysql> describe members ; describe groups ; describe group_user ;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(4)      | NO   | PRI | NULL    | auto_increment | 
| username   | varchar(65) | NO   |     |         |                | 
| password   | varchar(65) | NO   |     |         |                | 
| permission | int(4)      | YES  |     | NULL    |                | 
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(4)      | NO   | PRI | NULL    | auto_increment | 
| group_name | varchar(20) | YES  |     | NULL    |                | 
+------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| uid   | int(4) | NO   |     |         |       | 
| gid   | int(4) | NO   |     |         |       | 
+-------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

mysql> select * from members ; select * from groups ; select * from group_user ;
+----+----------+----------+------------+
| id | username | password | permission |
+----+----------+----------+------------+
|  1 | david    | desmo2   |          1 | 
|  2 | andrea   | pippo    |          0 | 
+----+----------+----------+------------+
2 rows in set (0.00 sec)

+----+------------+
| id | group_name |
+----+------------+
|  1 | david      | 
|  2 | users      | 
|  3 | andrea     | 
+----+------------+
3 rows in set (0.00 sec)

+-----+-----+
| uid | gid |
+-----+-----+
|   1 |   1 | 
|   1 |   2 | 
|   2 |   2 | 
|   2 |   3 | 
+-----+-----+
4 rows in set (0.00 sec)

 

So if I want to know which groups david is inside :

 

mysql> select group_name  from members   inner join group_user on group_user.uid=members.id inner join   groups on group_user.gid=groups.id where members.id=1 ;
+------------+
| group_name |
+------------+
| david      | 
| users      | 
+------------+
2 rows in set (0.00 sec)

 

I'm not very sure of this ...

[markwillis82]

yep. although I would make the group_user a primary key of uid,gid (both columns) this will mean each user can only be in each group once - and allow the use of indexes

 

also "password" if your storing a hash (md5) you can make the column CHAR(32) instead of varchar. (but this depends on what you are storing)

 

 

[kickstart]

Hi

 

enkidu72 - that looks fine, although you might want to use outer joins just in case a person is not a member of any groups. Points about keys above is a good point.

 

slightly off topic - kickstart... are you also on BCF? I used to be a member (not sure if my account is still up) it was "drummer"

 

Yep, I am on BCF still. Can't see an account for drummer still there, but thought they only disappeared if there were no posts.

 

All the best

 

Keith

[markwillis82]

ah - after more searching on the member list, my username was kram

[kickstart]

ah - after more searching on the member list, my username was kram

 

I do remember you now I have looked. FZR with the same colour scheme as mine.

 

All the best

 

Keith