Here is some vanilla SQL:
CREATE TABLE RoomTypes
(
RoomType VARCHAR(12) NOT NULL,
UNIQUE (RoomType)
);
CREATE TABLE Zones
(
Zone VARCHAR(10) NOT NULL,
UNIQUE (Zone)
);
CREATE TABLE Rooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);
CREATE TABLE NumberedRooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
CHECK (RoomType = 'Numbered'),
UNIQUE (Name),
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType, Zone, Name)
REFERENCES Rooms (RoomType, Zone, Name)
);
CREATE TABLE AncillaryRooms
(
RoomType VARCHAR(12) NOT NULL,
Zone VARCHAR(10) NOT NULL,
Name VARCHAR(30) NOT NULL,
CHECK (RoomType = 'Ancillary'),
UNIQUE (Zone, Name),
UNIQUE (RoomType, Zone, Name),
FOREIGN KEY (RoomType, Zone, Name)
REFERENCES Rooms (RoomType, Zone, Name)
);
The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.
The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.
Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.
I would say that if your users are going to need to query the Archive data, then using the bit flag or soft delete is easier. If the users don't need the data any longer, then I would go with the archive tables.
Based on your description above, I would suggest going with the Soft Delete version. I can tell you from experience in one of our systems, we went with an archive schema to move older data to and it lead to nothing but issues because the users needed access to the data. So it lead to using UNION ALL on every query we had to run.
As a result of the issues, we stopped that route and moved to the soft delete, which is much easier.
We added a bit flag to all of the tables it was needed and then we just included this in the WHERE clause when querying the data.
A suggestion would be to make sure that this field has a default value when you INSERT data. If you are using IsArchived then the default value on the column would be false since you do not want it archived immediately.
Best Answer
I have a few observations and suggestions:
Do not create a
DATEtable when all you need is an element (column) for the date of thePOST. Generally speaking, a table with nothing but a meaningless ID and one other column is a good indication that you need to look hard at whether all you really need is the non-key column.Consider
CATEGORY, this also has just its ID and the category description. Is this a good candidate for an independent table? There are two things to think about:On another subject: Your model indicates that a
POSTcan be written by manyUSERs. I'm not sure that this is really possible in your system, since you don't say anything about collaborative authorship. However, one thing that is almost certainly a problem is that each user can only ever author one post. I'm sure that's not what you meant.To fix this you either need to move the foreign key in the relationship between
USERandPOSTto the post side or you need to create an intersection entity if posts can have multiple authors.Similarly... Your business rules state that posts can have multiple
CATEGORYandTAGassignments, however, your model doesn't reflect this. In your model each post can have exactly one of each of these.To fix this you need to implement a many-to-many relationship for each of tags and categories. This will result in an intersection table in each case.
Lastly: Why is some of your user information segregated into another table with a many-to-many relationship to the main user table? There may be good reasons to keep password information in a separate table from other user information, but I don't see any reason stated in your business rules. Also, it isn't likely that password will be many-to-many with your user record.