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 ******************************************************