It also helps to know how you want to access/manipulate your data and what you want to do with it in SQL versus with your application. For instance, you could just store all the cad data as a blob, along with the attributes in columns.
Then you can fetch the data based on attributes, and manipulate the blob within your application. But if you expect to manipulate the coordinate info within SQL then you will need to export some or all of it into columns. For example it might be enough to just export the bounding box of each object into an rtree index so you can select objects from a region. Or you may need more detailed coordinate info available. So, the model you use needs to support the ways you plan to access and manipulate the data. -Steve W Jeffrey Becker wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
