List:General Discussion« Previous MessageNext Message »
From:Robert M (Bob) Bartis Date:April 2 2005 9:45pm
Subject:RE: [Q] Database design
View as plain text  
Sounds like you need a 1:N relationship table to hold userInfo separate from either the
user or group table. Adding a infoIdentifier would allow the number of rows added for a
specific user to be sized based on the specific user needs. This is effect would be the
"key" part of a key-value pair, normally associated with associative arrays. The
userSpecificInformation would hold the value portion of the information.

UserInfo table:
id (pk)
user_id (fk)
infoIdentifier
userSpecificInformation

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

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

UserInfo table:
id (pk)
user_id (fk)
infoIdentifier
userSpecificInformation


-----Original Message-----
From: Eric Gorr [mailto:mailist@stripped]
Sent: Saturday, April 02, 2005 2:59 PM
To: Tom Crimmins
Cc: mysql@stripped
Subject: Re: [Q] Database design


Tom Crimmins wrote:
> 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

Yes, if I understand what you are saying here correctly, I considered this.

However, the problem is that the columns corresponding to "any info 
specific to individual user/group combo" is not guaranteed to be 
consistent across groups. Well, to be more precise, the type for each 
column will be the same, but the number of required columns (call this 
number N) will be different.

It is for this reason that it seemed necessary to have a separate table 
per group.

Now, if I could decide what the maximum number of required columns would 
be, then I could see using this design, but this is simply not possible.

I am, of course, limited by the maximum number of columns (call this 
number X) allowed within a mySQL database. The required number of 
columns for a particular group could be anywhere between 1 and X. 
However, it just seemed like a bad idea to use that large of a table 
when the vast majority of it would go unused and much of it would likely 
never be used at all. But, perhaps I am wrong and it would simply not be 
an issue.

I suppose it would be possible to dynamically size 'usergroup table' 
based on the current max N across all groups. Basically, if N changes 
for a particular group, look at the value of N for all groups, take the 
max and size 'usergroup table' accordingly.

Is this what you would do?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
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