I'm looking for any general suggestions on how to best represent some data. This can be either at the database level or the application level. I'm open to any suggestions here, my current approach seems rather cumbersome.
I'm building a user interface for a retirement system model. The model allows you to build up a benefit formula from various parts: constants, expressions, averages, lookup tables, to name a few. The user interface allows you to create and edit a constant, an expression, and so on. Every model part has a name, all other necessary data fields are specific to each part type (constants have a name and a value, expressions have a name and a expression string, averages have a couple other fields, etc.). One thing I would like enforced - either at the application level or the database level, is that part name be unique across all part types (i.e. it is not allowed to have a constant named Foo and an average named Foo).
I'm struggling to easily represent this at the database level. My current appraoch (reflected in the subject line) is to have a table of "names", and then a table for each part type. The part type tables have a foreign key relationship with the names table. Something like this (I'm using sqlite3 at the moment, but would like as generic a solution as possible):
(type_id would work like: 1 for constant, 2 for expression, ...)
Code:
create table tbl_names (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
type_id INTEGER NOT NULL
);
create table tbl_constant (
id INTEGER PRIMARY KEY,
name_id INTEGER NOT NULL UNIQUE,
value REAL NOT NULL,
FOREIGN KEY(name_id) REFERENCES tbl_names(id)
);
create table tbl_expression (
id INTEGER PRIMARY KEY,
name_id INTEGER NOT NULL UNIQUE,
expr TEXT,
FOREIGN KEY(name_id) REFERENCES tbl_names(id)
);
This sort of works. It isn't perfect. There is nothing stopping me from creating a name "Foo" with type_id=1 (we'll give it id=1), and then creating a constant and an expression record, both with name_id=1. The application knowns the name "Foo" belongs to a constant because of the type_id=1, but still, this seems like a downside of my current approach.
A bigger downside is that this is just a total pain to model in the application. I've been working on a desktop application using Qt, and can sort of hack it together using Qt's model/view framework, but it feels cumbersome. Now I'm playing with models in django and am revisiting the issue all over again. It looks like maybe an inline formset would work with my existing database structure, but I haven't played around enough to get it working. So, before I dive in much further, I wanted to stop and get some opinions.
How can I better/best model this data to:
- enforce as clean of data as possible at the database level
- make application-level modeling as simple as possible
I imagine there could be some trade-off between these two objectives, so maybe I can't max them both out. This is a personal project, so there aren't really and hard and fast requirements that I can or cannot change.
Thanks in advance.
