How would you solve this problem...

[Administrator]

We have a single database, unlimited users. We log basic information in a spreadsheet, i.e. standard columns and rows in a data grid. However, the challenge comes in now that we want to allow EACH user to create custom columns of their own. So the basic columns of Date, First Name, Last Name are all there, but for you, one of the users, you want to add: Date of Birth, Anniversary, Type of Car, Phone Number. And me, another user in this same database I want to add: Hair color, Eye color, Height, Weight BUT I don't want to see other's custom columns, for me, I just want the basic columns AND "my" custom columns.

So how in a single database, shared among many users, would you design your application with a grid so that each user can have their own custom columns and NOT have these columns appear for other users. Custom columns are isolated by user (UserID).

 

[giadich]

it's not a good practice to put all these in one single column but it can be done.

Use another table to store appropriate column header names and add foreign key to the users table. As for the actual column, name it using a generic name (ie: strColumn1)

That way you'll have access to both the data and the correct column header.

 

[cjard]

We have a single database, unlimited users. We log basic information in a spreadsheet, i.e. standard columns and rows in a data grid. However, the challenge comes in now that we want to allow EACH user to create custom columns of their own. So the basic columns of Date, First Name, Last Name are all there, but for you, one of the users, you want to add: Date of Birth, Anniversary, Type of Car, Phone Number. And me, another user in this same database I want to add: Hair color, Eye color, Height, Weight BUT I don't want to see other's custom columns, for me, I just want the basic columns AND "my" custom columns.

So how in a single database, shared among many users, would you design your application with a grid so that each user can have their own custom columns and NOT have these columns appear for other users. Custom columns are isolated by user (UserID).

Sounds like you have an O/R mapping problem. However, you can either:

Dynamically create a table using ADOX or Jet DDL SQL that has the extra columns, relate that to the userid and form your SQLs dynamically:

CREATE TABLE base(
  TheDate DATE, First Name TEXT(50), Last Name TEXT(50)
)

CREATE TABLE ext_neal(
   Hair color TEXT(50), Eye color TEXT(50), Height NUMBER, Weight NUMBER 
)

CREATE TABLE ext_fred(
  Date of Birth DATE, Anniversary DATE, Type of Car TEXT(50), Phone Number TEXT(50)
)

queries, bear in mind that each ext table will have only one row:

SELECT * FROM base, ext_neal WHERE base.UserID = 'neal'


This query must be formed dynamcally:

String.Format("SELECT * FROM base, ext_{0} WHERE base.UserID = '{0}'", Environment.UserName)

Note that I always advocate NOT doing this, but this kind of query cannot be parameterised (well the 'neal' string can, but there's not much point given that the other occurence of neal cannot be)


---

Or you can go the route that I would actually advocate, with a table that looks like:

USER_ID, PREF_NAME, PREF_VALUE
neal, date, ...
neal, first name, ...
neal, last name, ...
neal, hair color, ...
neal, eye color, ...
neal, height, ...
neal, weight, ...

cjard, date, ...
cjard, first name, ...
cjard, last name, ...
cjard, date of birth, ...
cjard anniversary, ...
cjard, type of car, ...
cjard, phone number, ...


And then use an access PIVOT query to turn it columnar if you cant deal with it row-ar