Creating database tables with "either or" type fields.

Question

I have a database that tracks players through their attempts at a game. To accomplish this, I keep a table of users and store the attempts in a separate table. The schema for these tables is:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,  -- the local unique ID for this user
  name TEXT UNIQUE,  -- a self-chosen username for the user
  first_name TEXT,  -- the user's first name
  last_name TEXT,  -- the user's last name
  email TEXT,  -- the user's email address
  phone TEXT  -- the user's phone number
);

CREATE TABLE trials (
  timestamp TIMESTAMP PRIMARY KEY,  -- the time the trial took place
  userid BIGINT, -- the ID of the user who completed the trial
  score NUMERIC,  -- the score for the trial
  level NUMERIC,  -- the difficulty level the trial ran at
  penalties NUMERIC  -- the number of penalties accrued in the trial
);

Now I need to be able to store attempts that come from "transient" users. These attempts should not be linked back to an existing user. However, these transient users will still be able to enter a name that displays in the results. This name is not required to be unique in the table, since it does not represent a "real" user.

My first thought was to create a new field in the trials table called name. If userid is null, I would know it is a transient user, but I would still be able to show the name fields in the results. This approach doesn't quite smell right, and it seems like it will make my queries a bit more complicated. Additionally, it seems like I'm duplicating data in a sense.

Another thought was to replace userid with a useref text field that would be some kind of formatted string representing the user. For example, if the value were enclosed in curly braces, I would know it's an ID, i.e. {58199204}. If the value were not enclosed, I would treat it as a transient user. This more accurately represents what I'm trying to do conceptually (i.e. it's either an ID or a transient user string), but it would really complicate my queries.

I'm using SQLite for the backend... It lacks some of the extended features of SQL Server or MySQL.

Any thoughts on these or another approach to the problem?

Answer 1

You can either create a UserType field in the users table, and add "transient" users to the Users table, but this might increase the size of the Users table, or create a UserType field on the Trials table and create an additional TransientUsers table.

This will allow you to distinguish the difference of userid with the UserType field.

Answer 2

SQLite lets you mix types in a field. I'd suggest you do as you were thinking, but without the braces. Disallow numeric names. If the userid is a number, which is exactly when it matches an id in the users table, it is a user id. If not it's the name of a transient user.

Answer 3

Without more information about why a transient user can use but not exist in the system, I concur with your idea to:

1. Add a NAME column to the TRIALS table
2. Make the USER_ID column in the TRIALS table nullable/optional in order to indicate transient user status

If you could allow a transient user to exist in the system, I would recommend:

1. Creating a USER_TYPE_CODE table
2. Update the USERS table to include the USER_TYPE_CODE column (w/ foreign key reference to the USER_TYPE_CODE table)

Answer 4

I'd like to point out that you really shouldn't use the formatted string approach. What happens if a user finds a bugged input port into your database and inputs "{8437101}" (or whatever user ID they want)?