Is there a pattern to deal with a set of predefined rows in one or more tables that can also have user-defined rows inserted in them?
I could want to distribute an application with a table called Colors where I defined some colors that are, for example, used in the application's GUI. I might use one column for a numerical primary key, one for the name and three other columns for R,G,B values. I would put an unique constraint on the name column and one for the three R,G,B columns. The contents of this table are intended to be available to the user so that when he needs to specify a color he can choose one from the table. However it makes sense to allow the user to add his own colors to the table.
Issues however could arise if the user wants to:
- define a color whose R,G,B value are already used in a predefined color
- use a name that is already in use by a predefined color
- hide predefined colors he's not interested into
I need to have some predefined, read-only, rows on which the application can rely but at the same time I don't want to impose anything on the user while providing him with the opportunity to use some of the predefined rows if he chooses to.
I thought of separate tables or a single table with extra columns that specify if the row is predefined, hidden by the user and so on. However I can see flaws in both approaches.