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
