Is this a good design?
No, not really.
Some of the tables are not well designed.
Take a look at the responsibilities of the users
table and the media
table.
The media
table has one clear responsibility:
store the attributes of media items.
Every record is self-contained, complete.
You will probably never update the records in this table:
you will only insert or delete. That's nice and simple
The users
table has two responsibilities:
- Store the attributes of users
- Store the relationship of users and other objects
Every record is only half-complete (user attributes),
to get the full picture you need to follow the references to other tables.
You will probably never update the records very frequently,
for setting the last status and location ids
It would be better to make the users
table in charge of user attributes only.
You could add additional tables to track the relations with other objects.
The circular dependencies are especially ugly:
users
depends on status
for the last status, and status
depends on users
to associate all statuses with a user
- Same for
locations
I recommend this kind of structure:
user
- in charge of user attributes (name, registered date)
status
- in charge of status attributes (message, date)
user_status
- in charge of linking user and status
user_last_status
- in charge of linking user and last status, with unique key on user
And similarly for locations.
It's more work, but the end result will be more robust and flexible.
A reasonable compromise can be to not create user_status
,
but include user_id
in the status
table (with a similar treatment for locations too).
Select all users who belong to group and all groups created after that group.
To find users by a given group id fast,
you need an index on the group id column in the users table.
Below I explain how to create foreign keys.
When you make user.group_id
a foreign key referencing group.group_id
,
creating such index is one of the steps,
and will ensure fast lookups.
In addition,
you might also find a group fast by name.
In that case you might want to create an index on group.name
column.
However, if you won't have many groups than that would be overkill,
and not recommended by the documentation.
Select all users with last message, status, location and media URLs included.
Generally speaking, For good performance of queries like this,
you want to make sure that all fields used in the WHERE
and JOIN
clauses are indexed.
If you follow my suggestions,
this will be automatically the case for most of these elements you mention,
and for the rest you can probably figure out yourself.
Another issue with the design is that some of the fields are not clear enough, for example:
users
userid
: it would be good to know if this will be a technical id or a something like a UNIX username. If the latter, I'd rename to username
name
: NULL
is allowed, but why?
lmessage
: an integer field, but not clear which other table it will reference
registered
: I suppose the date when the user was created. I recommend using the type datetime
, even if internally it might be just an integer
locations
serial
: technical id I suppose?
locationid
: text? That's a bit surprising. How is it different from serial
?
time
: perhaps there can be a better name for this. And again, I recommend using the type datetime
status
: similar objections as with locations
messages
sno
: technical id I suppose? I recommend to standardize the name of technical ids in all tables
sender
, receiver
: I'm wondering if these have any relation to other tables
media
: this is good and clear. The UNIQUE
keyword on mediaid
is pointless when you add a PRIMARY KEY
constraint on the same column
The answers to the questions I raised would be good in comments.
Some example values for the columns would be useful too,
especially for the ones where the meaning and motivation is not obvious.
Declare PRIMARY KEY
on the field's line
Instead of declaring PRIMARY KEY
at the end,
it's more compact to declare it on the field itself, like this:
CREATE TABLE IF NOT EXISTS locations
(
serial INTEGER NOT NULL PRIMARY KEY,
-- ...
);
I also added NOT NULL
for the field, though it's not strictly necessary.
When a field is primary key in sqlite,
it's treated as auto-incrementing,
so even if you try to explicitly insert NULL into it,
it will actually use max(col) + 1
instead.
But I like to write it this way anyway to make it explicit:
looking at this schema there will be no doubt that the field will never contain NULL.
Pointless unique indexes
Many of the indexes you're adding seem completely pointless:
CREATE UNIQUE INDEX IF NOT EXISTS id_unique ON users (userid ASC);
CREATE UNIQUE INDEX IF NOT EXISTS serial_unique ON status (serial ASC);
CREATE UNIQUE INDEX IF NOT EXISTS id_unique ON messages (sno ASC);
CREATE UNIQUE INDEX IF NOT EXISTS mediaid_unique ON media (mediaid ASC);
In all these commands, the target fields are already marked as PRIMARY KEY
,
so adding a unique index will do absolutely nothing.
From the docs:
In most cases, UNIQUE and PRIMARY KEY constraints are implemented by
creating a unique index in the database. (The exceptions are INTEGER
PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables.) Hence, the
following schemas are logically equivalent:
CREATE TABLE t1(a, b UNIQUE);
CREATE TABLE t1(a, b PRIMARY KEY);
CREATE TABLE t1(a, b);
CREATE UNIQUE INDEX t1b ON t1(b);
There is one more unique index in your post:
CREATE UNIQUE INDEX IF NOT EXISTS serial_unique ON patterns (serial DESC);
But the schema of the patterns
table is not in your post.
Curiously, this unique index uses the DESC
keyword,
which would set it apart from the other examples which all used the default ASC
.
In this case, I'm wondering what difference this will make on a table where the target column is already a primary key.
That might be an interesting question on stackoverflow.com
Although in this case it seems the unique indexes are redundant and can be removed,
if ever you need to make a column unique,
I recommend declaring UNIQUE
on the column's line when creating the table,
like this:
CREATE TABLE IF NOT EXISTS status
(
serial INTEGER NOT NULL PRIMARY KEY,
userid TEXT NULL UNIQUE,
-- ...
);
Or else,
if you really want to use a unique index with DESC
sorting order,
then create the index right after creating the table.
It's not a good idea to separate the declarations of tables and indexes like in your post. The indexes are closely tied to their tables,
and the code is most readable when dependent elements are closed to each other.
Also keep in mind that only versions of SQLite 3.3.0 understand indexes with descending sorting order.
Use foreign keys
It seems you wanted to use foreign keys just didn't know how :)
Here's an example:
CREATE TABLE IF NOT EXISTS users
(
userid TEXT NOT NULL PRIMARY KEY,
locationid INTEGER NULL REFERENCES locations (locationid)
);
CREATE TABLE IF NOT EXISTS locations
(
serial INTEGER NOT NULL PRIMARY KEY,
locationid TEXT NOT NULL UNIQUE,
);
CREATE INDEX users_location_index ON users(locationid);
Notice the UNIQUE
constraint on the referenced column:
this is a requirement of foreign keys.
(For more details, see the docs.)
Lastly, notice the index on users.locationid
.
Although not required, this is recommended by the docs.
Indicate the column sizes
Although SQLite understands and ignores VARCHAR(N)
,
treating it as TEXT
,
I recommend to use VARCHAR(N)
anyway for non-text fields.
This is to indicate your design intentions.
It will also make it easier to port your schema to other databases in the future.
To be safe, add a comment to remind that SQLite doesn't enforce the specified sizes.
Naming and style
I recommend using id column names like user_id
, status_id
instead of userid
and statusid
because this seems a common practice.
I would borrow some ideas from Django:
- Use singular instead of plural for table names: for example
user
instead of users
- Use
id
as the auto-incrementing technical id field consistently in all tables
- Very cosmetic thing but anyway: write SQL types like
INTEGER
and TEXT
using lowercase letters