Look-up Tables: Table design
1 July 2008To do everything that I want to do with the look-up table service, I’m actually going to need three database tables in total: two for the metadata about the tables, and one for the actual data. The second metadata table will be needed to store information on the optional additional properties that are needed on occasion for those tables that are more than just simply a label and a value. The three entities to be used will be:
- LookupTable (the table definition)
- LookupTableProperty (the additional property definitions)
- LookupTableEntry (the actual values, or data, for the look-up tables)
The table definition itself will be relatively straightforward: a table id, a name, and possibly a description. To define an additional table property will require a little more data. I’m thinking that, at a minimum, we’ll need the following:
- id
- table id
- name
- type
- size
- label
- column heading
- source
- notes
The type is an interesting one, since it will definitely come from a limited set of choices such as String, Number, Date, etc, which would logically imply some sort of drop-down or look-up table, which would then make it the logical first choice for a table in our look-up table system. It will probably need to be a complex table as well, with a couple of additional fields, as certain types might require slightly different rules. For example, consider the following:
| id | label | size | source |
|---|---|---|---|
| T | Text | 250/0 | Y |
| N | Number | 11/0 | N |
| DN | Decimal | 11/4 | N |
| D | Date | 0/0 | N |
| DT | Date/Time | 0/0 | N |
While this is not a complete set of all possible property types, it illustrates both the kinds of data that could be used for additional table properties as well as an example of how such additional properties might be employed. In this case, the additional property size is used to specifiy the maximum value allowed on either side of the decimal point and the additional property source is used to indicate whether or not the values for this type of property can come from a pick-list of some kind. Data from such a table can be used to render the data entry element for each property defined.
The last table needed will be the table that holds the data itself. Like the table definition table, this one is pretty straightforward, but will contain just a few more elements:
- id
- table id
- entry id
- label
- additional properties (0-n)
That should just about do it. To make this a reality, we’ll need a database schema, some matching Java POJOs, and maybe a few mapping files to move the data from SQL to Java to XML, but we’ve done all of that before, so that shouldn’t be too difficult to do again for these three entities.
At least that’s the theory, anyway …
Sorry, the comment form is closed at this time.
