Database design for values which are sometimes predefined and sometimes custom

Question

In part of a website I am designing, there is a section that will have both predefined values and the option to specify custom values.

As a simpler example, let's assume that the values are "Sports" and that the application has predefined sports, but that some users will want to enter their own since I can't predict everything that they will come up. Users won't have access to the sports that others have entered, only their own.

Should the predefined sports and the custom sports be stored in the same table? In this case, I either have to allow a user_id column in that table to be null, or create a user and user_id for the predefined sports.

Most of the time when searching for a sport by name I will want to search both predefined and the user's own custom sports, so if the predefined and custom sports are separated, I would still have to have the search go across both tables.

On the other hand, if they are separated, it makes it harder to specify foreign key relationships based on a sport_id.

What is a good way to approach this type of situation?

Answer 1

I don't see a problem including both predefined and custom values in a table (perhaps you could use a bit flag to indicate "custom," in case you need to do something with them later). And you could easily specify an identity value as the primary key in that table, so there should not be an issue with specifying foreign key relationships.

The only issues you're going to have are dupes and mispellings.

Answer 2

I would implement this as a many-to-many relationship. Table 1 - sports (sport_id, sport_name, system_defined) Table 2 - users (user_id, user_name) Table 3 - users_x_sports (user_id, sport_id)

If space is not at a premium, the users_x_sports table joins users to the user defined sports and the system defined sports.

If space is at a premium, then use a UNION to search the system defined and user defined sports. Only link users to their user defined sports.

The space savings in normalizing out the sports and reducing duplicates may make up for the extra links in the users_x_sports table.