I have a database that I created for our office to hold things like the staff list, staff bios, calendar events, web page contacts, and various things like that. The original database was a bit of a mess, because I didn't really know much about normalization. After reading rudy's book, I redid almost all of the database, but there's one table I never got around to, mostly because it's not often used.
In my office, everyone is on a team. Teams are silly acronyms, like OBPT-30, or OHBC-2. In the original database, I had a table of these teams that sort of went like this:
These were all text fields, so a row would look something like this:
OBPT-30 | Bunny Petting Team | OBPT| Office of Bunnies | OBPT-30
The last field is to get the order show up right, so that OBPT-2 is ordered before OBPT-10 and not the reverse.
This is of course totally horrible, and I'm going to redo this table. But here's my question:
In my staff table, I have a team column, and right now it shows OBPT-30 (for example) instead of the ID number for OBPT-30. Normally I would make this numeric and join the teams table, BUT I almost never have to show the information in the teams table. 95% of the time the staff table is shown, I only need to show the team acronym (OBPT-30). There's only one page where I actually need to show the rest of the team information with a staff member, and when I do, I join the team_name column in the teams table with the team column in the staff table (both text fields).
Would it actually be worth changing this to numeric, then always having to join the teams table wherever the staff member is shown? It seems like it would actually be a performance hit over not having to join that table when the information I need is already in the table I'm showing.
nobody ever said that a table's PK has to be an integer
there's nothing wrong with using OBPT-30 as the PK
Woo! That saves some work
Now I just have to rewrite the one page, rather that a dozen.