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[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]
