Schema Pattern Name?

Databases
1

So I find myself using a particular technique that seems useful but I’m not sure what it is called. I’d like to research it a bit and see what the alternatives are but not sure where to start.

Have a game scheduling schema. Hobby project in production for almost 10 years. Games are played on a field so we have a game table and a field table.

field
…field_id - integer, primary key autoincrement
…field_key - char, unique description of the field

Now I already know what is being thought. If field_key is unique then why not use it as the primary key and get rid of field_id? Bear with me for a moment

game
…field_id - foreign key into field, can be NULL
…field_key - description of the field

Even worse. Basically have two foreign keys for the same relation.

But, here is the point. There are several use cases where no entry exists in the field table (and thus game.field_id = NULL) but game.field_key still contains a useful value.

  1. Field is To Be Determined (TBD). Not uncommon to schedule a game without knowing the exact field. Sometimes we know the park, sometimes only the city. I have tried putting TBD fields in the field table but they really aren’t fields and don’t seem to fit well. I also tried adding TBD flags to the game table but that just seems to add complexity. Setting game.field_key to something like “Huntsville TBD” and leaving game.field_id NULL seems to work well.

  2. Many games are imported from spreadsheets or other scheduling systems. Trying to use field_id in a spreadsheet is pretty much a no go. We encourage people to use field_key which is a unique description of the field. So when loading games, look up field_key in field and then establish the link.

Except that it’s difficult to get people to always use the exact field_key. All kinds of variations are used. Dublin 1, Dublin #1, D1 etc. I have tried to enforce a direct match but it just makes loading schedule more burdensome on the user. I have also tried field_alias tables but that just seems to add complexity. So once again, if the field_key is not found then set game.field_key and leave game.field_id as NULL. Later on users can come back into the schedule and associate the game to an actual field.

  1. Sometimes we have temp fields created just for a game or maybe a small tournament. It’s often just not worth the effort to add an entry to the field table if the field is never going to be used again.

Apologies for the long post. Just wondering if there was a name for this technique and if anyone could suggest alternate approaches.

2

I think the approach of having TBD as a field_key in the field table and removing the field_key from the game table is a good one, but it seems the real problem is more about controlling user input.

What about using a form for entry instead of excel? The field_key could be a drop down. For that matter, you could have an excel file where the field_key is a drop down.

3

Thanks for the response.

The system can actually generate an excel file with pull downs for everything but few people use it. It’s just the nature of the site. Likewise, they can enter using forms but with hundreds of games that quickly becomes tedious and error prone.