[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    [sqlite] Pilgrim seeking wisdom on schemas
From:       "Keith Herold" <keith.herold () comcast ! net>
Date:       2004-12-29 19:40:59
Message-ID: 003101c4edde$528f6b70$6401a8c0 () oafish
[Download RAW message or body]

I am looking for some inspiration on schemas in SQLite.  I am trying to
decide whether the following is a good idea, because I can't think of a way
to do this in SQLite without writing some C++ code.

I have a table that consists of warning and error messages that come from
two separate (but related) sources of operations and sets.  In other words,
any paricular operation can generate warnings and errors, and any particular
set can generate warnings or errors.

CREATE TABLE tblWarningsAndErrors
(
   SourceID INTEGER NOT NULL, --links to source table id; for sets, this is
tblSets.SetID;
                              --  for operations this tblOps.OpID
   MessageType INTEGER, -- one of:  1: SetWarning, 2: SetError, 3:
OpWarning, 4: OpError
                        --  1,2 go to tblSets; 3,4 go to tblOps
   Message VARCHAR(900)
);

The Set table:

CREATE TABLE tblSets
(
   SetID INTEGER PRIMARY KEY,
   --...
);

CREATE TABLE tblOps
(
   OpId INTEGER PRIMARY KEY,
   --...
);

My midgit-brained idea was to keep all the warnings and errors in a single
table, and then use the MessageType to decide which of the two tables to
access in order to link up the tables.  However, I can't think of a way to
make the decision in SQLite entirely in SQL  (in something like
transact-sql, I could execute a stored procedure).  Is this even possible,
without writing some C/C++/Java, whatever, code in SQLite?  Is this
generally just a bad design, or a normal thing?  Most of me thinks that the
design principle involved probably necessitates a separate table for set
warnings and errors, and another one for operation warnings and errors.

--Keith

******************************************************
- Ever notice how 'big' isn't, compared to 'small'?

- Sounds like a Wookie; acts like mad cow.

- I'm not a professional; I just get paid to do this.

- Rules for programming:
   1.  Get it working, right?
   2.  Get it working right.

- Things I've learned about multithreaded programming:

    123...   PPArrvooottieedcc ttm  ueelvvteeirrtyyhtt
rhheiianndgge  dwi hnpi rctohhg eri aslm omscitanalgt 
 iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb
.ee^Nr waicscee snsoetd  'aotb jtehcet -slaomcea lt'il
m^Ne from two or more threads
******************************************************


[prev in list] [next in list] [prev in thread] [next in thread]