Re: [sqlite] DB design questions
My 2 cents: create table Objects(id integer primary key, name text); create table Attributes(id integer primary key, name text); create table ObjectAttributes(objectID integer, attributeID integer, order integer, type integer, value text, version text); - Original Message - From: "Michael Ruck" <[EMAIL PROTECTED]> To: <sqlite-users@sqlite.org> Sent: Friday, April 20, 2007 10:10 PM Subject: [sqlite] DB design questions Hello, I'm currently modelling and designing a new database according the following specifications. The general idea is the ability to store arbitrary objects, which have attributes of various kinds. The attributes themselves may be multivalued. The objects being stored need to be versioned, so that there's a way to go back to previous versions of an object. The objects represent metadata of media files and the data itself comes from various automated sources and manual editing by a user. My current idea was the following: - CREATE TABLE objects (id TEXT, version TEXT) - CREATE TABLE attributes (object_id TEXT, version TEXT, name TEXT, order INT, type INT, value TEXT) Is there anyone who has experience with this kind of design, do you have better ideas on modelling this kind of data? Thanks, Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB design questions
[EMAIL PROTECTED] wrote: Hello, I'm currently modelling and designing a new database according the following specifications. The general idea is the ability to store arbitrary objects, which have attributes of various kinds. The attributes themselves may be multivalued. The objects being stored need to be versioned, so that there's a way to go back to previous versions of an object. The objects represent metadata of media files and the data itself comes from various automated sources and manual editing by a user. Michael, That approach will work OK for the simple types (str, numbers, dates, bool ) attribute. And also it assumes fairly flat object structures. How are you going to deal with the attribute which is some kind of Collection as you mentioned multivalue, or worth if your attribute points to another object ? You can represent collection as String and later parse them, but I think it kind of defeats the purpose. You can also serialize your object as XML and store those serializations as strings or Blobs. All in all the SQL only databases are not very good dealing with OO data. You either store fairly simple Objects into the DB or spend a lot of time on Mapping OO to SQL -- by time I mean a development time to design and implement those mapping and then runtime penalty to assemble and disassemble your object structures for your application to and from DB. For that you may need to take a look at some Object- relational extensions of the databases -- Oracle has it, PostgreSQL does have extensions. Or you may need to use pure OO DB or XML databases. Just My 2c. My current idea was the following: - CREATE TABLE objects (id TEXT, version TEXT) - CREATE TABLE attributes (object_id TEXT, version TEXT, name TEXT, order INT, type INT, value TEXT) Is there anyone who has experience with this kind of design, do you have better ideas on modelling this kind of data? Thanks, Mike -- Mark Pirogovsky "Computers made it possible to make millions mistakes a second" "Ask no questions and hear no lies" - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB design questions
By having "versioned attributes" do you mean the entire set of attributes is versioned as a group, or individually? Is it an object's state at a specific point in time that you want to version? --a On 4/20/07, Michael Ruck <[EMAIL PROTECTED]> wrote: Hello, I'm currently modelling and designing a new database according the following specifications. The general idea is the ability to store arbitrary objects, which have attributes of various kinds. The attributes themselves may be multivalued. The objects being stored need to be versioned, so that there's a way to go back to previous versions of an object. The objects represent metadata of media files and the data itself comes from various automated sources and manual editing by a user. My current idea was the following: - CREATE TABLE objects (id TEXT, version TEXT) - CREATE TABLE attributes (object_id TEXT, version TEXT, name TEXT, order INT, type INT, value TEXT) Is there anyone who has experience with this kind of design, do you have better ideas on modelling this kind of data? Thanks, Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DB design questions
Hello, I'm currently modelling and designing a new database according the following specifications. The general idea is the ability to store arbitrary objects, which have attributes of various kinds. The attributes themselves may be multivalued. The objects being stored need to be versioned, so that there's a way to go back to previous versions of an object. The objects represent metadata of media files and the data itself comes from various automated sources and manual editing by a user. My current idea was the following: - CREATE TABLE objects (id TEXT, version TEXT) - CREATE TABLE attributes (object_id TEXT, version TEXT, name TEXT, order INT, type INT, value TEXT) Is there anyone who has experience with this kind of design, do you have better ideas on modelling this kind of data? Thanks, Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DB design questions
We do something like that by storing the data in TEXT format and using RCS to handle versioning by its reverse delta method. It works well for storing notes and may be useful in your application. A function can return the appropriate version. Michael Ruck wrote: Hello, I'm currently modelling and designing a new database according the following specifications. The general idea is the ability to store arbitrary objects, which have attributes of various kinds. The attributes themselves may be multivalued. The objects being stored need to be versioned, so that there's a way to go back to previous versions of an object. The objects represent metadata of media files and the data itself comes from various automated sources and manual editing by a user. My current idea was the following: - CREATE TABLE objects (id TEXT, version TEXT) - CREATE TABLE attributes (object_id TEXT, version TEXT, name TEXT, order INT, type INT, value TEXT) Is there anyone who has experience with this kind of design, do you have better ideas on modelling this kind of data? Thanks, Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
