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.
You need to increase the amount of RAM, by a LOT. Based on the load you are expecting I'd want at least 64 Gigs in there, if not 128 or 256. Given that most of the database will become pretty stale pretty quick I'd say that one database should be just fine. I've got tables with billions of rows which are OLTP. Granted they are MSSQL, not MySQL but the ideas are the same.
Best Answer
1.
payment
(black box)1.1) No currency information?
In the
Payments
table, although you store anamount
anddate
, you store no currency. Perhaps you plan on only charging in one currency, but I feel that method will only last so long if this is intended to scale.1.2) What is this second
payments
table?What is the second
payments
table for? The one below and slightly left of the actualpayments
table? It seems to have only anid
column, which is the only column in this diagram to specify a data type!? I'm guessing this is some diagram artefact.1.3) You'll need more than just the
name
to take payments!The
payment_methods
table currently doesn't store the type (i.e. credit card, paypal, BitCoin, whatever) of the method. There also seems to be no linked table of payment_types for this either.You will need to store stuff like credit card numbers, etc somewhere (hopefully not in this database!). You will need some sort of linking information for those stored payment methods to be kept in the
payment_methods
table.2.
social
(blue box)2.1) Who exactly is
me
?I'm assuming the
Me
table is some sort of façade or profile type of table. Perhaps allowing a givenUser
to shield themselves from the embarrassment of letting their collage/university friends see theMusic
they were into in high school? :PI do not believe it makes sense to double store the first, middle and last names of the user. It would be much better to go with a solution like that which @beeks suggested in the comments above.
2.2)
My_Profile_Details
... field?I cannot figure out why this is a single field. I assume it's some sort of
memo
type, for storing a lot of text (i.e. a CV of sorts). I think this needs MUCH more decomposition. At a minimum, into Sections of some sort.There also seems to be nothing in a
Proflie
that allows a user to select what Schools/Collages/Universities/Enterprises they studied/worked at, etc?3.
classmates
(red box)3.1) Internationalisation?
This part of your design is missing information regarding country. Even if you're only looking for schools/colleges in a particular county, employers (which is what I assume
Enterprise
is for) may be in any country.Also, If a classmate starts their own enterprise, do they have the same relationship to that enterprise as someone who works for it?
3.2) Institution Names
You have assumed Universities (and I suppose high schools, primary schools and collages too) have only one name, and that name is in English (or whatever language you're intending to store). It is quite common for universities to have different names in different languages. Institution names can also change over time (renamed, etc).
3.3) Institution Decomposition
Not all Universities divide themselves into Faculties, some are simply a collection of
Schools
(think Oxford, etc). These can also change name over time. Also, both faculties (schools), universities (and probably collages, primary and secondary schools too) can merge (i.e. get taken over/bought out) over time.3.4) Institution Location Multiplicity
Your design seems to assume a University can only be in one
town
. Many Universities have different campuses in different towns. Some even have campuses across international borders.