Re: [sqlite] Database design and SQLite
michael munson schrieb: Type refers to an int value that represents an in-server datatype. The main difference is that I realized that I wouldn't necessarily need a row for EVERY property, only if a property is different than the value stored on the parent object. If its the same, it can have no entry and the code will just look to its parent (and its parent's parent, and so on) until it finds the correct value to inheriet. I think that will reduce the number of rows significently, if add a couple of CPU cycles but I think it will make up for it in the querying. Look for typical use cases and try to count the SQL commands you need to execute to solve these use cases. For my application (an interactive GUI based client server application) I have found, that I at most have to get ALL needed data in 3 - 6 queries (where the upper end is critical ...). With SQLite I would expect (local database), that 6 is not criical at all. If you do need too many queries, "bring the data to the queries": change your layout until its ok. Otherwise the interaction of the GUI is not good and the user will not be happy. Organizing the application in tab pages also helps you - checking from one page to another means activity for the user and you can use this time for your SQL queries. If you have tree structure go the Explorer way of Windows - show initially, that the object have children - only if the user checks them, solve the reverenfences and update the GUI. My customer were not happy about this until I showed them, that Windows does it the same way. Then suddenly the customer were quite - even though they did not believe me until they saw it in Windows. Marten
Re: [sqlite] Database design and SQLite
You guys have been a lot of help. This is the idea I've gotten from the discussion we've had. Three tables: 1) Method table I did not mention this, because I pretty much already had the idea down. It will have the following columns: Where is a text entry that has stored some code in a language which this server will use, which is run through a tokenizer/lexer/parser/virtual machine when necessary. While the methods are inherieted as well, they're inherieted as-is, you cannot change a method on a child object unless you create a new one and overload a parent's method. 2) Object table 3) Property table Type refers to an int value that represents an in-server datatype. The main difference is that I realized that I wouldn't necessarily need a row for EVERY property, only if a property is different than the value stored on the parent object. If its the same, it can have no entry and the code will just look to its parent (and its parent's parent, and so on) until it finds the correct value to inheriet. I think that will reduce the number of rows significently, if add a couple of CPU cycles but I think it will make up for it in the querying.
Re: [sqlite] Database design and SQLite
Marten makes good points. I would go further and suggest that you look at creating your own API and DB primitives for your application. Often you will find that trying to shoehorn general purpose tools into tightly specific applications is as much effort as rigorously defining the problem and creating a purpose built solution which is tight, fast and easy to code. JS Marten Feldtmann wrote: Just some additional comments: The "vertical" approach (described by Teg) leads also to a very untypical relational database and if a pure sql administrator would look at it . Ok, but it works, but when using a vertical approach you have consider some points: * you have to throw away the column (attribute) constraints (contraint, unique) ... * accessing the data needs SQL commands, whichs stresses parts of SQL one normally would not use and perhaps you may find some bugs (group by) ... * insert speed increases pretty linear (when having ONE table for all objects - again different mapping may improve this). queries are not that bad. * you need tool support - otherwise you are lost. If you can read Germany - I've described the vertical approach in a presentation on page 28: http://www.schrievkrom.de/uk/tips/nordakademie-2004/Vortrag-Nordakademie-2004.pdf In this presentation there's also stuff about using/creating database portable object-ids and stuff like this. +++ The "XML" approach is - more or less - a marketing idea ... XML is now used for everything and the main problem of XML is: speed. +++ "SQL for Smarties" is a well written book - (by the way: the author is Joe Celko) +++ Another points FOR SQL oriented databases: they are initially open for ALL programming languages and therefore pretty different from all these language centric databases (mainly in the Java world). This is achieved by introducing an API - which every language has the same possibilities to get most of the database - and NOT by using language bindings. +++ If you have to consider tree structures you should consider the special SQL extension some databases offer: (SAP-DB, MaxDB, Firebird). These SQL extensions (sometimes called recursive sql selects) can improve the speed of your application in a DRAMATIC way ! Marten Jay Sprenkle schrieb: On 1/14/06, michael munson <[EMAIL PROTECTED]> wrote: Greetings, I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I have run into a bit of a wall while attempting to design a database for a C++ program I am attempting to write. The needs of the database are to: represent an object oriented hierarchy of any number of objects, where each object may have custom properties of several different datatypes and permission bits. What about defining a table called 'properties'. It would have a key to link to the object and 'name' and 'value' column for each object property. You could have as many properties as desired for each object and they need not be the same for each object. I do wonder the same thing as another poster. Is a database really the tool you want to be using for this? I can't imagine what you really need with a database for that application.
Re: [sqlite] Database design and SQLite
Teg schrieb: Hello Marten, Monday, January 16, 2006, 2:14:59 PM, you wrote: To me duplicate entries or near duplicate entries in a table are a no-no. Sounds to me like you're talking about inserting the object multiple times in the same table each instance describing one "property" of the object. What you're suggesting sounds like ... Which duplicates not only the ID but, the entries (from the original poster's requirements). Whereas I'm suggesting Table 1: Table 2: ... I don't belive you can do ... Because you really have no idea how many properties there are going to be. If you have one object with 2 properties and one with 100, you'll have 200 columns of properties even for objects that only have 2 properties. Now, if an object was ONLY properties then I agree, you could make a table of only properties but, he's using the object table to define some parent child hierarchy (It does sound like a sqliteized version of XML). Your suggestion is a possible extension - this table can be used to add additional columns (used for optimistic locking - as an example). Some futher thoughts about this: The column is a simple varchar(x) column and the software has to convert the attribute value in a possible and suitable string representation. An search index is set on this column (to search objects using some attributes). Therefore the conversion from the native datatype to the string representation has to be done carefully (as an example: how can one compare a negative number against a positive number with different number of digits and decimal points - when these numbers are only represented as a string ...). The modelling of associations are just the matter of using one single row for one object in an association (the property value contains the object id referenced by this association). If you have to model a :n association you may have multiple rows for this object for one . and so on, and so on please remember: it's not just a model of persistency. Perhaps useless, perhaps usefull in special situations. Marten
Re[2]: [sqlite] Database design and SQLite
Hello Marten, Monday, January 16, 2006, 2:14:59 PM, you wrote: To me duplicate entries or near duplicate entries in a table are a no-no. Sounds to me like you're talking about inserting the object multiple times in the same table each instance describing one "property" of the object. What you're suggesting sounds like ... Which duplicates not only the ID but, the entries (from the original poster's requirements). Whereas I'm suggesting Table 1: Table 2: ... I don't belive you can do ... Because you really have no idea how many properties there are going to be. If you have one object with 2 properties and one with 100, you'll have 200 columns of properties even for objects that only have 2 properties. Now, if an object was ONLY properties then I agree, you could make a table of only properties but, he's using the object table to define some parent child hierarchy (It does sound like a sqliteized version of XML). C MF> Teg schrieb: >>Hello Marten, >> >>I wasn't suggesting one table for all object, I was suggesting a table >>for objects and a table for object properties. Using the object ID as a way >>to identify which properties belong to what objects in the properties >>table. The "Vertical" part was simply for the object properties >>since, there is no predefined limit on the number of properties any >>object might have. >> >> >> MF> What do you store in the object table ? Why do you need the object table MF> ... just MF> use the property table ! The entries within the property table defines MF> the object ! >>It's not clear to me why a single properties table is a bad idea from >>an SQL standpoint. Is it that the properties can be read in a random >>order? >> >> >> MF> What did I learned from history - one may use Oracle as a storage MF> management and if it does not work, ok. If one uses PostgreSQL and MF> it does not work - bad for the person. MF> If I store the data in the normal teached way in relational MF> database ok, but if it wents wrong and a typical relational MF> database administrator may look at your database you MF> will be in trouble, because you use the tool in a way which MF> is very untypical - thats all I wanted to say. MF> Do whatever you want to do - beside the mainstream - but be MF> prepared to be attacked. I love relational databases and I like MF> to work with them very much. >>With an index on the object id's of the properties table, I'd even >>expect the performance to be reasonably good. >> >> >> MF> The time for the insert statement will grow linear - consider MF> this as a possible problem. The amount of bytes transfered to MF> the application will grow. You need several statements to insert MF> one object. Consider this as a timing problem in a network MF> environment. MF> But again: the vertical approach may be the way to go. MF> Marten MF> Marten -- Best regards, Tegmailto:[EMAIL PROTECTED]
Re: [sqlite] Database design and SQLite
Teg schrieb: Hello Marten, I wasn't suggesting one table for all object, I was suggesting a table for objects and a table for object properties. Using the object ID as a way to identify which properties belong to what objects in the properties table. The "Vertical" part was simply for the object properties since, there is no predefined limit on the number of properties any object might have. What do you store in the object table ? Why do you need the object table ... just use the property table ! The entries within the property table defines the object ! It's not clear to me why a single properties table is a bad idea from an SQL standpoint. Is it that the properties can be read in a random order? What did I learned from history - one may use Oracle as a storage management and if it does not work, ok. If one uses PostgreSQL and it does not work - bad for the person. If I store the data in the normal teached way in relational database ok, but if it wents wrong and a typical relational database administrator may look at your database you will be in trouble, because you use the tool in a way which is very untypical - thats all I wanted to say. Do whatever you want to do - beside the mainstream - but be prepared to be attacked. I love relational databases and I like to work with them very much. With an index on the object id's of the properties table, I'd even expect the performance to be reasonably good. The time for the insert statement will grow linear - consider this as a possible problem. The amount of bytes transfered to the application will grow. You need several statements to insert one object. Consider this as a timing problem in a network environment. But again: the vertical approach may be the way to go. Marten Marten
Re: [sqlite] Database design and SQLite
On Mon, Jan 16, 2006 at 04:11:47AM -0600, michael munson wrote: > >At the worst that would be around 6 million columns on a property table. > >I've not used SQLite with tables that large before > >so I'm not sure if searching with 2 index values (name, and object its on) > >is going to be slow. > > Heh, that should obviously read '6 million rows' . Sorry, its early. Unless your rows were exceptionally wide, that shouldn't be difficult to handle at all. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re[2]: [sqlite] Database design and SQLite
Hello Marten, I wasn't suggesting one table for all object, I was suggesting a table for objects and a table for object properties. Using the object ID as a way to identify which properties belong to what objects in the properties table. The "Vertical" part was simply for the object properties since, there is no predefined limit on the number of properties any object might have. It's not clear to me why a single properties table is a bad idea from an SQL standpoint. Is it that the properties can be read in a random order? With an index on the object id's of the properties table, I'd even expect the performance to be reasonably good. C Monday, January 16, 2006, 5:52:34 AM, you wrote: MF> Just some additional comments: MF> The "vertical" approach (described by Teg) leads also to a very MF> untypical relational MF> database and if a pure sql administrator would look at it . MF> Ok, but it works, but when using a vertical approach you have consider some MF> points: MF> * you have to throw away the column (attribute) constraints (contraint, MF> unique) ... MF> * accessing the data needs SQL commands, whichs stresses parts of SQL one MF>normally would not use and perhaps you may find some bugs (group by) ... MF> * insert speed increases pretty linear (when having ONE table for all MF> objects - MF>again different mapping may improve this). queries are not that bad. MF> * you need tool support - otherwise you are lost. MF> If you can read Germany - I've described the vertical approach in a MF> presentation MF> on page 28: MF> http://www.schrievkrom.de/uk/tips/nordakademie-2004/Vortrag-Nordakademie-2004.pdf MF> In this presentation there's also stuff about using/creating database MF> portable object-ids MF> and stuff like this. MF> +++ MF> The "XML" approach is - more or less - a marketing idea ... XML is now MF> used for MF> everything and the main problem of XML is: speed. MF> +++ MF> "SQL for Smarties" is a well written book - (by the way: the author is MF> Joe Celko) MF> +++ MF> Another points FOR SQL oriented databases: they are initially open for ALL MF> programming languages and therefore pretty different from all these language MF> centric databases (mainly in the Java world). This is achieved by MF> introducing MF> an API - which every language has the same possibilities to get most of the MF> database - and NOT by using language bindings. MF> +++ MF> If you have to consider tree structures you should consider the special SQL MF> extension some databases offer: (SAP-DB, MaxDB, Firebird). These SQL MF> extensions (sometimes called recursive sql selects) can improve the MF> speed of MF> your application in a DRAMATIC way ! MF> Marten MF> Jay Sprenkle schrieb: >> On 1/14/06, michael munson <[EMAIL PROTECTED]> wrote: >> >>> Greetings, >>> I'm a bit new to SQL and SQLite so pardon me if I ask silly >>> questions but I have run into a bit of a wall while attempting to >>> design a database for a C++ program I am attempting to write. >>> >>> The needs of the database are to: represent an object >>> oriented hierarchy of any number of objects, where each object may >>> have custom properties of several different datatypes and >>> permission bits. >>> >> >> What about defining a table called 'properties'. It would have a key >> to link to the object and 'name' and 'value' column for each object >> property. You could have as many properties as desired for each object >> and they need not be the same for each object. >> >> I do wonder the same thing as another poster. Is a database really the >> tool you want >> to be using for this? I can't imagine what you really need with a >> database for that >> application. >> >> >> -- Best regards, Tegmailto:[EMAIL PROTECTED]
Re: [sqlite] Database design and SQLite
On 1/16/06, michael munson <[EMAIL PROTECTED]> wrote: > Well, the main reasons I want to use a database as opposed to some other > type of arbitrary formatted file is real time access. > In the sense that I won't have to read the entire file into memory and can > just request objects as I need them and keep them in memory, > until a certain amount of time passes without them being used in which I can > free their memory, and if someone wants that object again it will > just be pulled out of the database. Commit/rollback functionality is also a > plus. sounds like you've given it a lot of thought. If you didn't need transactions I would suggest a hash and a sparse file. You could locate objects with a simple calculation and position to them with a single seek. There wouldn't be any index overhead. but for this application sounds like a database might be just what you need. Good luck!
Re: [sqlite] Database design and SQLite
Just some additional comments: The "vertical" approach (described by Teg) leads also to a very untypical relational database and if a pure sql administrator would look at it . Ok, but it works, but when using a vertical approach you have consider some points: * you have to throw away the column (attribute) constraints (contraint, unique) ... * accessing the data needs SQL commands, whichs stresses parts of SQL one normally would not use and perhaps you may find some bugs (group by) ... * insert speed increases pretty linear (when having ONE table for all objects - again different mapping may improve this). queries are not that bad. * you need tool support - otherwise you are lost. If you can read Germany - I've described the vertical approach in a presentation on page 28: http://www.schrievkrom.de/uk/tips/nordakademie-2004/Vortrag-Nordakademie-2004.pdf In this presentation there's also stuff about using/creating database portable object-ids and stuff like this. +++ The "XML" approach is - more or less - a marketing idea ... XML is now used for everything and the main problem of XML is: speed. +++ "SQL for Smarties" is a well written book - (by the way: the author is Joe Celko) +++ Another points FOR SQL oriented databases: they are initially open for ALL programming languages and therefore pretty different from all these language centric databases (mainly in the Java world). This is achieved by introducing an API - which every language has the same possibilities to get most of the database - and NOT by using language bindings. +++ If you have to consider tree structures you should consider the special SQL extension some databases offer: (SAP-DB, MaxDB, Firebird). These SQL extensions (sometimes called recursive sql selects) can improve the speed of your application in a DRAMATIC way ! Marten Jay Sprenkle schrieb: On 1/14/06, michael munson <[EMAIL PROTECTED]> wrote: Greetings, I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I have run into a bit of a wall while attempting to design a database for a C++ program I am attempting to write. The needs of the database are to: represent an object oriented hierarchy of any number of objects, where each object may have custom properties of several different datatypes and permission bits. What about defining a table called 'properties'. It would have a key to link to the object and 'name' and 'value' column for each object property. You could have as many properties as desired for each object and they need not be the same for each object. I do wonder the same thing as another poster. Is a database really the tool you want to be using for this? I can't imagine what you really need with a database for that application.
Re: [sqlite] Database design and SQLite
Indeed it may be questionable to use SQLite for stuff like this, but its a very fast relational db library - and therefore it can be used as any other relational database to store objects. What is needed is very simple: you need a object-oriented relation database wrapper - either as a commercial library or as a home written software. Be aware, that storing objects in relational database IS not a trivial task (concerning the fact, that you want to have good speed). Especially tree structures are always difficult to handle (in an efficient way). Some databases have special SQL commands extension for that. Tree structures are often used in the GUI applications. I've worked with Smalltalk and relational databases and yes it can be done, but going this way, one has to consider BOTH sides of the development: the object oriented side and the relational side and perhaps one should not use the most hottest feature in the OO world, because mapping can only be done very badly. Accept the relational side and then you may get happy. How to map oo structures into databases has been described in the literature over the last 15 years (starting with Smalltalk, then with Java and now with C# and all hottest languages) in many online arcticles, in many books etc. Making a good mapping assumes the knowledge about relational databases, about the application one has to write and the special needs of this application. In general: without tool support, you will get lost. As an example: I've over the year (in Smalltalk) used a commercial library, which allows me to define a oo model (classes, hierarchies, attributes, associations). Then it creates a "simple" relational database layout and the Smalltalk base code for the model. You may defined searchable attributes (leading to indices) and several different kinds of mappings: one class to one table, several classes to one table etc. Databases like this are (in newer days) called integrational databases, because they look like normal databases and the values can be retrieved from all other languages. Other databases are the application oriented databases - databases with - perhaps - strange layouts (in the normal sense of relational databases): one simple example is a table with two coloumns: one for the object id, the other for a BLOB column, holding the binary representation of the object). Now I go another way: I've written my modeller by myself and also wrote a code generator for the languages I wanted to support (in this case C#). Within the model each attribut, each association and each class gets a unique number (for management purposes). The data types supported are restricted to Integer, String, Floats, Decimal, Date, DateTime, Boolean - mainly the basic stuff. Even support for arrays are possible. Then I defined a general micro language to create a binary representation of my object and changed the modeller to create source code for this representation. The textual representation of ths language would be like (simplified) set objectid environment to set attribute 1 to value <12.23> set attribute 2 to value set attribute 3 to value <2006/01/20> add association at attribute 4 with object with close objectid environment This representation is pretty compact and can be created automatically and even the code to restore the object from that representation can be created automatically and pretty fast. Searchable attributes are (in addition) not only stored in this binary (BLOB) representation, but also in special tables: each searchable attribute has its own table and always the same layout: column1: object id, column2: attribute value. The name of this table may be created automatically using the attribute id of the attribute. (The drawbacks of this procedure are quite clear - several insert for ONE object. For a local database (file oriented) this may be not that critical, for a networked database this approach is more critical). Associations are handled using additional tables You see: there are standard ways of doing the work and very specialized ways of doing it Marten Jay Sprenkle schrieb: On 1/14/06, michael munson <[EMAIL PROTECTED]> wrote: Greetings, I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I have run into a bit of a wall while attempting to design a database for a C++ program I am attempting to write. The needs of the database are to: represent an object oriented hierarchy of any number of objects, where each object may have custom properties of several different datatypes and permission bits. What about defining a table called 'properties'. It would have a key to link to the object and 'name' and 'value' column for each object property. You could have as many properties as desired for each object and they need not be the same for each object. I do wonder the same thing as another poster. Is a database really the tool you want to be using for
Re: [sqlite] Database design and SQLite
At the worst that would be around 6 million columns on a property table. I've not used SQLite with tables that large before so I'm not sure if searching with 2 index values (name, and object its on) is going to be slow. Heh, that should obviously read '6 million rows' . Sorry, its early.
Re: [sqlite] Database design and SQLite
What about defining a table called 'properties'. It would have a key to link to the object and 'name' and 'value' column for each object property. You could have as many properties as desired for each object and they need not be the same for each object. That could be a solution, but at the upper bounds I'm expecting around 30,000 to 60,000 objects which each may have between 50 and a 100 properties. At the worst that would be around 6 million columns on a property table. I've not used SQLite with tables that large before so I'm not sure if searching with 2 index values (name, and object its on) is going to be slow. I do wonder the same thing as another poster. Is a database really the tool you want to be using for this? I can't imagine what you really need with a database for that application. Well, the main reasons I want to use a database as opposed to some other type of arbitrary formatted file is real time access. In the sense that I won't have to read the entire file into memory and can just request objects as I need them and keep them in memory, until a certain amount of time passes without them being used in which I can free their memory, and if someone wants that object again it will just be pulled out of the database. Commit/rollback functionality is also a plus. --michael
Re: [sqlite] Database design and SQLite
On 1/14/06, michael munson <[EMAIL PROTECTED]> wrote: > Greetings, > I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I > have run into a bit of a wall while attempting to design a database for a C++ > program I am attempting to write. > > The needs of the database are to: represent an object oriented hierarchy of > any number of objects, where each object may have custom properties of > several different datatypes and permission bits. What about defining a table called 'properties'. It would have a key to link to the object and 'name' and 'value' column for each object property. You could have as many properties as desired for each object and they need not be the same for each object. I do wonder the same thing as another poster. Is a database really the tool you want to be using for this? I can't imagine what you really need with a database for that application.
Re: [sqlite] Database design and SQLite
Thanks, I'll pick it up. The first thing to ask yourself here is whether or not it makes sense to use a database. Certainly that makes sense if you need concurrent read and write access, but if you only need to write from one source at a time an XML file sounds more like what you need. It supports your need for infinite hierarchy, and if you work without a DTD it allows setting of any properties that you would like. If you don't like the general bloat of an XML file you can make use of a library like libxml2 (http://www.xmlsoft.org) which natively and transparently supports compressed XML files. If you're absolutely sold on the need that this be in a database, buy a copy of Joe Selko's _SQL for Smarties_. It covers these hierarchical structures in great detail. Even after implementing this kind of structure before I wouldn't try it again without consulting Selko's book. Clay Dowling
Re: [sqlite] Database design and SQLite
michael munson wrote: > Greetings, > I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I > have run into a bit of a wall while attempting to design a database for a C++ > program I am attempting to write. > > The needs of the database are to: represent an object oriented hierarchy of > any number of objects, where each object may have custom properties of > several different datatypes and permission bits. > > The table fields that I have so far are a primary integer key (ID), text > representing the object's name, (NAME), and two fields "OWNER" and "PARENT" > which are integers representing other objects in this table. > > The problem I am running into is the object hierarchy and custom properties. > For example: If object #1 is a parent of object #2, and #1 has a property > named "location" then #2 should also have that property (although the value > and permissions may be different from the parent). > > I'm trying to see if I can do this some way I do not currently know how, > because the only thing I can think of is some delimited BLOB and recalculate > all the parent properties whenever the parent is changed which I imagine may > significently slow down my database. The first thing to ask yourself here is whether or not it makes sense to use a database. Certainly that makes sense if you need concurrent read and write access, but if you only need to write from one source at a time an XML file sounds more like what you need. It supports your need for infinite hierarchy, and if you work without a DTD it allows setting of any properties that you would like. If you don't like the general bloat of an XML file you can make use of a library like libxml2 (http://www.xmlsoft.org) which natively and transparently supports compressed XML files. If you're absolutely sold on the need that this be in a database, buy a copy of Joe Selko's _SQL for Smarties_. It covers these hierarchical structures in great detail. Even after implementing this kind of structure before I wouldn't try it again without consulting Selko's book. Clay Dowling
Re: [sqlite] Database design and SQLite
Hello michael, If I was doing that, I'd have another table of nothing but "properties". Each property would have an integer that represents which object the property belongs to. In that way, there's no limit to the number of properties you can assign to an object. In the case of your parent/child relationships, you're probably going to have to pull the records all the way up the chain and decide which one overrides the other programmatically. I don't think the child necessarily has to have the same list of properties as the parent (though this depends on your design) I was thinking the child's property might be the union of the parent and child's properties with whatever policy you use to resolve cases where they both have the same property but, different values. I'm not really an SQL expert either, but in cases where you have an indeterminate number of values for an item, I tend to think vertical table structure instead of having a bunch of columns you might or might not use. C Saturday, January 14, 2006, 12:35:19 PM, you wrote: mm> Greetings, mm> I'm a bit new to SQL and SQLite so pardon me if I ask silly mm> questions but I have run into a bit of a wall while attempting to mm> design a database for a C++ program I am attempting to write. mm> The needs of the database are to: represent an object mm> oriented hierarchy of any number of objects, where each object may mm> have custom properties of several different datatypes and mm> permission bits. mm> The table fields that I have so far are a primary integer key mm> (ID), text representing the object's name, (NAME), and two fields mm> "OWNER" and "PARENT" which are integers representing other objects mm> in this table. mm> The problem I am running into is the object hierarchy and mm> custom properties. For example: If object #1 is a parent of object mm> #2, and #1 has a property named "location" then #2 should also mm> have that property (although the value and permissions may be mm> different from the parent). mm> I'm trying to see if I can do this some way I do not mm> currently know how, because the only thing I can think of is some mm> delimited BLOB and recalculate all the parent properties whenever mm> the parent is changed which I imagine may significently slow down mm> my database. mm> Opinions? mm> Regards, mm> Michael Munson -- Best regards, Tegmailto:[EMAIL PROTECTED]
[sqlite] Database design and SQLite
Greetings, I'm a bit new to SQL and SQLite so pardon me if I ask silly questions but I have run into a bit of a wall while attempting to design a database for a C++ program I am attempting to write. The needs of the database are to: represent an object oriented hierarchy of any number of objects, where each object may have custom properties of several different datatypes and permission bits. The table fields that I have so far are a primary integer key (ID), text representing the object's name, (NAME), and two fields "OWNER" and "PARENT" which are integers representing other objects in this table. The problem I am running into is the object hierarchy and custom properties. For example: If object #1 is a parent of object #2, and #1 has a property named "location" then #2 should also have that property (although the value and permissions may be different from the parent). I'm trying to see if I can do this some way I do not currently know how, because the only thing I can think of is some delimited BLOB and recalculate all the parent properties whenever the parent is changed which I imagine may significently slow down my database. Opinions? Regards, Michael Munson
