2010-09-30, 00:17
smashmouse Wrote:Been using xbmc for a bit and thought I'd see if there is anything I can do to help with future evolution.
Regarding db schema design, i really wouldn't recommend using a EAV schema to model the whole logical data model. I think it's ok to model flexible attribute sets that maybe only used for display purposes. Maintenance and query design of such a schema will be a large overhead.
Has there been any thought of using a star schema to provide dimensional model of the data?
I've also been pushing back on a pure EAV/CR design, and firnsy agreed that a hybrid using explicit tables (e.g., movie) with flexible attribute sets attached to them as necessary (each table has a key and "extra" attributes can be attached via the table key and the table's own primary key) is a good compromise. My main issues with EAV/CR is that it breaks relational integrity (non-null columns, data type enforcement, indexes, foreign keys) and that it unnecessarily creates a database within a database and turns the application into a mini-DBMS, if you use it for columns that establish relationships, need sorting, etc. It's less bad as you say if they're only "display" columns (but still extra query overhead and lacks data type enforcement). I'd prefer to avoid EAV/CR altogether if there's no compelling case for it.
Can you explain more about where you think dimension tables would be useful? For the video database nullable columns seem sufficient for information that may not be available, and there are already separate tables for various relationships.