how do you do this ???

[Guest_imported]

i need to have a users table but I should be able to add information about the users very easely... (because for the moment I don't know what for information I'm going to have to store about these users)

so I have a table: USERS
--------------------------
userID, userGroupID (with a reference to a userGroups table)

and I have a table with name-value pairs: USERINFO
----------------------------------------------------
name, value

so this table *could* contain for example:

name value
userName Dave
password blabla
email blabl@blabla.com

but I don't know what way it is BEST to link 'em together...

1) i could add a primary key column to the USERINFO table , add a column to the USERS table and link it..
2) i could add the userID to the USERINFO table and find out what INFO there is about a specific user this way..

i don't know... anyone got some advice??? alternative ways to solve this problem are also welcome...

thanks....

 

[RickSpr]

Adding a primary key to USERINFO wouldn't work, because the relationship goes one USERS to multiple USERINFO rows. You need to either add UserID to USERINFO, or add a primary key (such as an Autonumber) to USERS and a Long Integer to USERINFO to link them.

If there's a chance that a UserID would change, it might be better to add an Autonumber. Then you don't have to cascade the changes to the USERINFO table. But if UserID won't change, putting UserID directly into USERINFO will avoid having to use both tables to look up information about the user.

Here's an entirely different approach. You thought of using USERINFO because you won't know until later what information you have about a user. But you could add columns to the USERS table as you discover new information you need. You can do this in VBA code by building and executing SQL DDL (Data Definition Language) statements. For instance, to add a column named to the table with the following statement:
ALTER TABLE USERS ADD COLUMN Email Text(30);

You can add columns to a table even after it has data in it, provided the columns you add do not have the NOT NULL characteristic.

Adding columns to USERS this way would give you the ability to retrieve all the information about a user with a single query--something you can't do with your 2-table method. Rick Sprague