List:General Discussion« Previous MessageNext Message »
From:Tom Crimmins Date:April 2 2005 7:43pm
Subject:Re: [Q] Database design
View as plain text  
On Saturday, April 02, 2005 12:48, Eric Gorr wrote:

> Peter Brawley wrote:
>> Eric,
>> 
>> If I understand you correctly, you propose to encode user and group
>> info as table names. That's a mistake. To use an RDBMS like MySQL
>> effectively, you want to encode your information as rows of data in
>> tables whose names and structures do not vary.
> 
> Thank you for your comments. Would this design be better?
> 
> ( assume that one of the group names will be 'Group_A' )
> 
> Database
> 
>    Table_Groups
>      group name
>      # of user columns
> 
>    Table_Group_A_users
>      username
>      Column 1 Data
>      Column 2 Data
>      ...
>      Column N Data
> 
> I am still encoding group info into a table name, but I am unsure of
> how to avoid this and not have a table with a lot of wasted space.
> 
>> May I suggest you read
>> some of the tutorials listed at
>> http://www.artfulsoftware.com/dbresources.html, and/or read
>> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf.
> 
> Thank you for the pointers.
> 
> Unfortunately, http://www.artfulsoftware.com/dbresources.html seems to
> have a couple of broken links (Codd's Rules)...any idea where this
> information can be found?

user table:
id (pk)
name
any other user info only dependant on the user

group table:
id (pk)
name
any other group info only dependant on the group

usergroup table:
user_id (pk)
group_id (pk)
any info specific to individual user/group combo

pk = primary key

This third table is called a linking table. It allows you 
to deal with a many-to-many relationship. This setup allows 
a group to have multiple users, and users can belong to 
multiple groups. You will need to look into joins to see 
how to query these tables effectively.

For example to find out what users are in group A.

SELECT u.name FROM user as u 
INNER JOIN usergroup as ug ON (u.id = ug.user_id) 
INNER JOIN group as g ON (ug.group_id = g.id)
WHERE g.name = 'A'

-- 
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
Thread
[Q] Database designEric Gorr2 Apr
  • Re: [Q] Database designPeter Brawley2 Apr
    • Re: [Q] Database designEric Gorr2 Apr
      • Re: [Q] Database designPeter Brawley2 Apr
Re: [Q] Database designTom Crimmins2 Apr
  • Re: [Q] Database designEric Gorr2 Apr
RE: [Q] Database designBob)2 Apr
Re: [Q] Database designEric Gorr3 Apr