On Wed, Jul 06, 2011 at 01:41:13PM +0100, e-mail mgbg25171 scratched on the wall: > The layout where x and y are dimensions eg
> might be represented the following fact table > xid yid data > If I added another dimension eg yy > might be represented by this extended fact table > yy xid yid data > That's all well and good until you decide you want to DROP the yy dimension. > How do you best organise table to represent a star schema type arrangement > so that you can easily INSERT/DELETE dimensions and the new data that > accompanies them. > Hope I've made myself clear. If you have a reasonable, bound number of dimensions, I would just create a column for each dimension. You can insert NULL for any values that don't have a value in a specific dimension. This also gives somewhat fine grain control over your queries, as you can search for values with specific values, or with specifically no value (e.g. "x IS NULL"). If you have a more dynamic set of dimensions, then a "star" schema is exactly right. You want to setup attribute tables for the data rows. You could have a different table for each dimension, or a single attribute table with a (dimension, d_value, data_id) type configuration. In that case, you would have one row per dimension per data value. Regardless, each dimension has a reference back to the data row it represents. The issue with attribute designs is that they can be very tricky to query. You often find yourself needing to do a "relational divide", which is kind of the opposite of a JOIN. It is one of the core Relational operations, but it isn't supported natively by SQL. There are ways of doing it in SQL, but the query syntax can get quite messy. I would put together a few example cases of what you're trying to do with your application. In addition to the data layout, pay specific attention to the types of queries you need to run and how you're going to set those up. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
