The first question(s) I would ask are: Are all the fields case sensitive? (according to your definition they are) Are any of them, other than the primary key, unique? (according to your definition they are not) Other than the isPersonal column all of the columns permit a NULL entry. Is this your intent (it is what is written) One would presume (based on the English meanings of the column names) that the groupid groups multiple projects together. How do you intend to insure the group name is correct since you are repeating it in every record. Do you not think it may be more appropriate to have a table of groups, and have the projects.groupid be a foreign key into the groups table?
Using "big long inscrutable strings or binary blobs" for keys is relatively unimportant compared to answering the questions above. You could also just use great big long strings of random data as keys (which is what a uuid is). It will only serve to make it difficult for a human to debug your database. If you are going to do that, then create a separate table of uuid to simple-integer-sequence keys. That way the uuids are kept where they belong, completely outside of the need for human viewing. Yet you can still use them as unique long strings to attach to simple abstract keys if you are so addicted. (I have a set of big rusty pinking shears for use on people who use uuid's as keys. It is almost impossible to "just look" at something that uses uuids as keys and be able to tell what you are looking at (though it is a little better on my 104" monitor, but it is still impossible to "see" relations. Using simple small integers is far better, though YMMV if you have eidetic memory.) > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Cecil Westerhof > Sent: Saturday, 12 December, 2015 15:32 > To: SQLite mailing list > Subject: [sqlite] Putting an index on a boolean > > I am playing with SQLite. I am thinking about writing an application for > projects. At the moment I have the following: > > CREATE TABLE `projects` ( > `projectID` TEXT PRIMARY KEY, > `groupID` TEXT, > `isPersonal` INTEGER NOT NULL CHECK(isPersonal in (0, 1)), > `name` TEXT, > `description` TEXT, > `outcome` TEXT > ); > CREATE INDEX projects_groupID_idx > ON projects(groupID); > CREATE INDEX projects_isPersonal_idx > ON projects(isPersonal); > > ?I like to differentiate between personal and non personal projects. Is it > a good idea to put a index on isPersonal? > > > By the way: I am thinking about using UUID for projectID and groupID, but > I > heard somewhere that it was a bad idea to use UUID for an indexed field. > Is > this true?? > > -- > Cecil Westerhof > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
