CODEWITHSUNDEEP
databasesqlitedatabase-design
tonmischa
tonmischa

Reputation: 26

Database Design - how to store the relationship between Items and their intended use

Long-Time Reader, first-time poster...

I am trying to come up with a database design for a backup application. I have dabbled a bit with SQLite before, but I'm not a professional programmer. So I am wondering if my database design makes any sense at all...

My app (written in Xojo) is basically a backup application, that backs up all disks it can find, except the ones which are explicitly excluded by the user.

In the (SQLite) database I have to keep track of all disks and some of their properties. So I have a Table Disks:

CREATE TABLE Disks (
 id INTEGER NOT NULL UNIQUE,
 Name TEXT NOT NULL,
 DiskType INTEGER NOT NULL,
 NameTag TEXT NOT NULL,
 LastSeen TEXT,
 PRIMARY KEY ( id ),
 FOREIGN KEY ( DiskType ) REFERENCES DiskTypes( id ) ) ;

The DiskType describes if its a local or a network volume (which has to be mounted via a script).

CREATE TABLE DiskTypes (
 id INTEGER NOT NULL UNIQUE,
 DiskType TEXT NOT NULL,
 PRIMARY KEY ( id ) ) ;
INSERT INTO DiskTypes ( id, DiskType ) VALUES ( 1, 'Network' );
INSERT INTO DiskTypes ( id, DiskType ) VALUES ( 2, 'Local' );

Now comes the "tricky" part.

The user will be presented with a list of all volumes that have ever been connected to that computer - stored in the table "Disks".
And then he is able to change the status of any disk to "Backup Source", "Backup Destination" and "Excluded".
There can be more than one volume of status "Backup Destination" and the user can choose which "Backup Source" will be backed up to which "Backup Destination".
(basically Disk1 backs up to Disk80, Disk2 backs up to Disk81, Disk3 backs up to Disk80 again, and so on.)

So I created a table for the possible "Use".

CREATE TABLE UseTypes (
 id INTEGER NOT NULL UNIQUE,
 UseType TEXT NOT NULL,
 PRIMARY KEY ( id ) ) ;
INSERT INTO UseTypes ( id, UseType ) VALUES ( 1, 'Source' );
INSERT INTO UseTypes ( id, UseType ) VALUES ( 2, 'Destination' );
INSERT INTO UseTypes ( id, UseType ) VALUES ( 3, 'Excluded' );

And here is my approach for the table that holds the information for the relationship between the Disks and their intended use:

CREATE TABLE DiskUse (
 id INTEGER NOT NULL UNIQUE,
 DiskID INTEGER NOT NULL UNIQUE,
 UseID INTEGER NOT NULL,
 LastBackup TEXT,
 BackupToDiskID INTEGER,
 PRIMARY KEY ( id ),
 FOREIGN KEY ( DiskID ) REFERENCES Disks( id ),
 FOREIGN KEY ( UseID ) REFERENCES UseTypes( id ),
 FOREIGN KEY ( BackupToDiskID ) REFERENCES Disks( id ),
CHECK ( 
  ( UseID = 1 and BackupToDiskID <> DiskID )
  or ( LastBackup = NULL and BackupToDiskID = NULL ) ) ) ;

My question is:
- Does it make sense to keep track of the Disk Usage in this one big DiskUse Table ? (the columns "LastBackup" and "BackupToDiskID" would only be needed, if a disk is a Backup Source (UseID = 1) and I would have to check if the chosen Backup Destination is in fact a disk that has been set to "BackupDestination"... )
- Or would it make more sense to have 3 Tables DisksSource, DisksDestination and DisksExcluded and move the disks between them ? ( This design would allow for duplicates, which does not make sense...)
- Or is there a third option I haven't thought of ? Maybe I'm thinking way too complicated here ?

Any help is appreciated !

Upvotes: 0

Views: 103

Answers (1)

poaca
poaca

Reputation: 76

In general it makes sense to me. However, it might be better to move the UseID to Disks table. This way you don't have to create any records on the DiskUse table for Destination and Excluded disks. Only thing you will loose from your current schema would be the check statement on DiskUse table which in itself might be problematic if depending on how you do the inserts. If this check has to exists you can change it to;

CHECK ((BackupToDiskID <> DiskID )
       or ( LastBackup = NULL and BackupToDiskID = NULL ) ) ) ;

This will still give you some utility.

Upvotes: 1

Related Questions