-
Notifications
You must be signed in to change notification settings - Fork 4
Database
Caleb Sander edited this page Jan 29, 2021
·
13 revisions
"Hello noobs and doobs. It is I, Sir Rahul of Donut. It is my utmost dismality to welcome you to the team of devs and of masters of artful literature. Please feel free to ping me anytime regarding sweet bagels with a hole." - Rahul Bachal, December 17, 2017
The database has three users with three levels of power ascit
, devel
, and root
. There are three databases: donut
, donut_dev
, and donut_test
. donut
is the live database used by the site. donut_dev
is used by the devteam for development purposes. donut_test
is used during testing.
-
mysql -u <user> -p <database>
to log in (-p
denotes prompt for password) -
show databases;
to show the databases. -
use donut_dev;
to use the donut_dev database. -
show tables;
to show all the tables in a database. -
mysql -u <user> -p <database> < file.sql
to run the SQL script file.sql
psql ascit devel
There are several principles underlying the schema design which we would like to maintain for new tables/columns:
- Design around
NATURAL JOIN
s. Because of the schema design, most queries that require combining entries in multiple tables can be written by simplyNATURAL JOIN
ing the tables involved. This requires that (1) the same column names are used for the same values in different tables and (2) tables do not otherwise have conflicting column names. For example, we would avoid a column name likeid
orname
, as many tables are likely to have similar columns. Instead, it is preferable to prefix these names with the type of data stored in the row, e.g. useoption_id
andoption_name
. - Avoid using multiple tables with redundant information in 1-one-1 relationships. For example, the legacy database spread the data now stored in the
members
table across several tables linked together by user ID (inum
) or UID. This made simple queries of member data cumbersome to write because they required several (often unNATURAL
) joins. - Use
INT
ID columns (rather thanVARCHAR
) to make ID comparisons faster - Be cautious when adding
NULL
able columns. There should be a clear reason why the column could beNULL
that is not just a lack of data. This means we don't have to handle as many cases of whether data is present when querying the database. - Use MySQL
VIEW
s to encapsulate common queries, e.g.current_position_holders
for (in)direct position holders that are currently active, orhouse_positions
for positions that represent house memberships.