ORM is always a tricky business. My experience is that unless the entity is very very simple, it's often inappropriate to attempt to do 1 row = 1 entity mapping. If things are collections of points, then by all means have a points table. A lot of this type of stuff can be made easier by 1) really understanding your object-model first and 2) comming up with good mapping strategies for your objects.
On Tue, Sep 30, 2008 at 2:08 PM, Jeff Godfrey <[EMAIL PROTECTED]> wrote: > Hi All, > > I've got some general db storage design questions that I hope someone > can offer some advice on... > > I have a need to store some CAD-type geometry in a SQLite database, and > I'm trying to decide on the best db design. Specifically, I need to > store "entity" data, which will include such things as points, lines, > arcs, circles, and text data. Along with the physical coordinate data > that defines each entity, I also need to store various "attributes" > about each entity (color, layer, style, font (for a text object), etc)... > > Ideally, I'd like a single record to tell me everything about a given > entity. So, one option would be to create a unique table for each > entity-type in question, with columns as appropriate. While that makes > sense to me on the surface, I also have the need to "step through" the > geometry in an ordered fashion. Obviously, I could keep some kind of > "entity order" table with references to each entity and the table it was > stored in, but then I don't see a clean way to walk through the geometry. > > For instance, my "order" table might direct me to first get a line from > the line table, then a circle from the circle table, then a text string > from the text table. While this isn't too difficult to accomplish via > program logic, it seems a bit "messy", which has me wondering if there > might be a better way. > > Since the table for each unique entity type would contain (at least > some) columns unique to the specific entity, I don't think there's a way > to combine the tables into a single, ordered view that could be easily > "walked", is there? > > The other thought I had was to create a simple "POINT" table, and store > all the points that make up every entity in that one table. Then, I'd > need a way, per entity, to reference which points belonged to the > current entity. So, a line would reference 2 records in the POINT > table, an arc would reference 3 POINT records, etc. > > One obvious drawback to this approach is that now there's not a single > record that contains an "entire" entity, as there would be in the first > approach. > > Since I'm not an expert in this arena, I'm hoping that I'm missing an > obvious solution. Any thoughts appreciated. Also, if you need further > clarification on any of the above, feel free to ask. > > Thanks for any input. > > Jeff > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
