- Our site has 3 tables in our main database, ‘test_database’:
- rough_cuts
- table_login
- users_roughCuts
rough_cuts - this table deals with information about Rough Cuts that are submitted via the Rough Cut selector tool. When a person creates a Rough Cut, values for ‘name’, ‘submitted_by’, ‘filename’, ‘hashtag’ and ‘description’ are entered. The columns ‘name’ and ‘description’ are based on user input, while ‘hashtag’ is generated through javascript and added to the database using Math.random(). ‘submitted_by’ is taken from a session variable used during login to determine which user is submitting the Rough Cut. ‘rc_id’ is generated automatically based on a time stamp, this is so that Rough Cuts can be displayed in chronological order when they are displayed on a user’s main dashboard (i.e. the most recently created Rough Cuts will be displayed first). The ‘votes’ column is based on data that is inserted and updated on the main Rough Cuts page. When someone clicks on the heart icon, an AJAX call is made to this column to get the current number of votes a RC has, and then updates this number by 1.
Figure 1. Screenshot of rough_cuts table
table_login - a simple two-column table that stores username and passwords. Data is inputted after a user signs up to the site on index.php. ‘id’ column is set to auto increment.
Figure 2. Screenshot of table_login table
users_roughCuts - unlike in the main rough_cuts table which only stores the user a Rough Cut has been submitted by, this table is used to map which Rough Cuts have been pinned by other members of the site. The table contains 4 columns, ‘hashtag’, ‘username’, ‘pinned’, and ‘voted’.
Figure 3. Screenshot of users_roughCuts table
This information is intended to be used to display different Rough Cuts on member dashboards like the one below. Initially we intended to also allow users to keep track of Rough Cuts they had voted on (thus the need for a ‘votes’ column), but this feature was cut out of our implementation in the interest of time.
Figure 4. Screenshot of a member dashboard. ‘Pinned’ Rough Cuts currently being shown. Other option is to see created Rough Cuts.
When a user ‘pins’ a Rough Cut on the main Rough Cuts page by clicking the pin icon (see Figure 5 below), their username, the hashtag value of the clicked element as well as the value pinned=1 gets inserted into the database.
Figure 5. Screenshot of a Rough Cut on the main Rough Cuts page. Pinned icon appears only to logged in DIY Trove members.
Having this information stored in a separate table enabled us to make subqueries like the one below to easily display which Rough Cuts had been pinned or created by different members.
- SELECT name, hashtag, filename, submitted_by FROM rough_cuts WHERE hashtag IN (SELECT hashtag FROM users_roughCuts WHERE pinned='1' AND username='$username')
