Proper Relational Database Design

[Alphathree33]

Say I have a table of Groups.

Say I have a table of People.

Say each person can be assigned to multiple groups, but I don't know how many (if any) groups each person will be assigned to.

Is there a good way to do this? Currently my only idea is to make a "Groups" field in "People" and then use comma-separated ID numbers to indicate which groups e.g. 2,25,37

 

[Haircut]

You would need to add a third table called something like people_groups

This would have columns person_id and group_id, and these two make up the primary key in people_groups

person_id and group_id would also reference person_id and group_id in the people and groups tables respectively.

 

[Alphathree33]

Okay I've got another one for you.

Why doesn't this VB code work?

--------
SQL = "SELECT * FROM (Sessions INNER JOIN SubSessions ON (SubSessions.Session1 = Sessions.ID OR SubSessions.Session2 = Sessions.ID)) ORDER BY Sessions.Name, SubSessions.Name;"
Set RS = Conn.Execute(SQL)

' .....

Response.Write( RS("Sessions.Name") )
---------

If I say Response.Write( RS("Name") ), it works (and outputs SubSessions.Name) but if I use Response.Write( RS("Sessions.Name") ) or Response.Write( RS("SubSessions.Name") ), it says "Item cannot be found in the collection..." blah blah blah.

I thought INNER JOIN was supposed to take the records from both tables... so why is it seemingly only grabbing SubSessions and also, even if it were, why does SubSessions.Name produce a VB error?

 

[oog]

It should bring in the columns from the other tables. It looks like the columns are not named as you would expect. Why don't you specify the actual columns (since you are also doing then when you read the recordset anyway), and alias them to what you really want them to be named? It's a better practice anyway to specify the columns you want to select.

 

[Barnaby W. Füi]

Google for "database normalization," it is important stuff, and there's some good reading on the web about it.

 

[Cat]

/me chants BCNF.