-1

So I'm new to database structure, and I am currently making an app. I wanted to run the structure by you guys to make sure this is the optimum way to build the database.

So the app, is basically for several experiments for learnings. So basically, you login, and you are presented with various tasks, then you create a session. For which you need to finish to do any other task. Each trial is recorded, and when the session is finished (determined by the number of trials, then the app returns you to the menu.

  CREATE TABLE `users` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `username` varchar(255) NOT NULL,
 `firstname` varchar(255) NOT NULL,
 `lastname` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(60) NOT NULL,
 `created_at` timestamp NOT NULL,
 `updated_at` timestamp NOT NULL,
 `remember_token` varchar(100) NOT NULL,
 `status` varchar(100) NOT NULL,
 `priviledges` varchar(100) NOT NULL,
 `rewards` int(3) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `sessions` (
 `id` int(100) NOT NULL AUTO_INCREMENT,
 `user_id` int(100) NOT NULL,
 `task_type` varchar(255) NOT NULL,
 `firstresponse_datetime` timestamp NOT NULL,
 `lastresponse_datetime` timestamp NOT NULL,
 `max_trials` int(4) NOT NULL,
 `current_trial` int(4) NOT NULL,
 `status` int(2) NOT NULL,
 `wasted_stimuli` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `trials` (
 `id` int(255) NOT NULL AUTO_INCREMENT,
 `session_id` int(255) NOT NULL,
 `transfer` tinyint(1) NOT NULL,
 `sd` varchar(255) NOT NULL,
 `sample` varchar(255) NOT NULL,
 `comparisons` varchar(255) NOT NULL,
 `response` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So here's the thing. I am concerned that the app is reliant on too many tables at once. Like, the general app is that, a page is presented, a post request is made and the data gets entered into the database. A page between trials is presented, then the user is redirected to a new trial page. So within a matter of a 10-15 seconds, the use will make roughly 2 trials.

My concern is that the server is a webserver, and this will be part of a php script. The trials will be INSERT, the session will be UPDATED and the user might get UPDATED to by tokens. But tokens might be part of the session table. To reduce UPDATE from 2 to 1 table only, with only 1 insert.

Also I'm concern about the wasted_stimuli row. This will basically be an array that would be updated and extended every trial. And at the beginning of every trial it is checked. So I'm either retrieving the wasted_stimuli array from sessions or I perform a SELECT sd WHERE JOIN SESSIONS AND TRIALS BY ID to session_id based on the user. and then place sd in an array.

I'm not sure what is the best standard for this.

Thanks! Any other things that could make it better would be interested in knowing.

Let me know if you have any questions

Drew
  • 24,851
  • 10
  • 43
  • 78
JP Foster
  • 1,725
  • 4
  • 17
  • 23

1 Answers1

1
CREATE TABLE `users` (
 `id` int NOT NULL AUTO_INCREMENT,  -- display widths don't move me
 `username` varchar(255) NOT NULL,
 `firstname` varchar(255) NOT NULL,
 `lastname` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(60) NOT NULL,   -- you need to think about this, I will provide a link
 `created_at` timestamp NOT NULL,   -- or datetime. I use them
 `updated_at` timestamp NOT NULL,
 `remember_token` varchar(100) NOT NULL,
 `status` varchar(100) NOT NULL,
 `privileges` varchar(100) NOT NULL,    -- spelling
 `rewards` int NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `sessions` (
 `id` int NOT NULL AUTO_INCREMENT,
 `user_id` int NOT NULL,
 `task_type` varchar(255) NOT NULL,
 `firstresponse_datetime` timestamp NOT NULL,
 `lastresponse_datetime` timestamp NOT NULL,
 `max_trials` int NOT NULL,
 `current_trial` int NOT NULL,  -- my guess is that these need to be added as FK's
 `status` int NOT NULL,
 `wasted_stimuli` text NOT NULL,
 PRIMARY KEY (`id`),
 CONSTRAINT fk_user_to_parent FOREIGN KEY (user_id) REFERENCES users(id) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `trials` (
 `id` int NOT NULL AUTO_INCREMENT,
 `session_id` int NOT NULL, -- my guess is that these need to be added as FK's
 `transfer` tinyint(1) NOT NULL,
 `sd` varchar(255) NOT NULL,
 `sample` varchar(255) NOT NULL,
 `comparisons` varchar(255) NOT NULL,
 `response` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Mysql Using FOREIGN KEY Constraints

As mentioned in schema, passwords issues. Proposed schema at bottom of that. And links off that.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • 1
    it comes down to data quality. The more FK's the better. You need more FK's. Comments to such are sprinkled above – Drew Nov 07 '15 at 17:36
  • Alright, thanks for the foreign key notice. I never knew what those were but it doesn't seem to be super important. I think it will be worthwhile to put in. What about performance, does the table seem like a legit structure? What can be done to imporverr persformance? – JP Foster Nov 07 '15 at 20:12
  • ah are you saying `wasted_stimuli` is a CSV, because I won't rewrite [This Answer](http://stackoverflow.com/a/32620163) – Drew Nov 07 '15 at 20:26
  • if you dont think FK's are super important, I beg to differ. If you want to load your tables with junk, have at you. If you think arrays are stored in db's, I beg to differ. If you want performance as you say, look into Junction Tables with indexes. Link was in the prev comment – Drew Nov 07 '15 at 20:31
  • we are not here to be clapping clearleaders. Part of it is to ask why, give experience, question the question. If you want confirmation without review, there is the bathroom. It probably has a mirror – Drew Nov 07 '15 at 20:35
  • Sorry about that, I didn't mean to get thee wrong impression. From what I can tell their important insofar as when things get updated or change, there are exception to deal with the previous data. I'll look into junction tables. Thanks. I really do appreciate it! – JP Foster Nov 07 '15 at 20:47
  • Yes wasted_stimuli is a CSV. However, I'm not sure if this is actually the way to go. Basically, at the beginning of each trial, the program will make sure that no words in the wasted_stimuli CSV will be randomly picked by the program. So that each trial is new. I suspect I would have to regex, to put it back into an array each time, and extend it each trial. im really not sure if this is the standard way to do it. Any advice would be greatly appreciated! – JP Foster Nov 07 '15 at 20:49
  • ok, I was a little over the top there. Sorry. Look into junction tables or a mechanism to not store arrays in a column. Just please dont do an array in a single column – Drew Nov 07 '15 at 20:50
  • There is a Junction table for many to many seen in the above link to course and students: there are course and students. That is many to many. So there is a junction table to say what has what. There is nothing wrong with a table A with various rows in table B, such that there is not a junction table #3 (meaning 2 tables max not 3). Just don't save arrays or csv in a table. That is a nightmare – Drew Nov 07 '15 at 20:55
  • One cannot outperform the link referenced in comment #3. Peers do comment please. Those that find life in `find_in_set()` and `group_concat()` the way to go – Drew Nov 07 '15 at 20:59
  • So are you saying, instead of wasted_stimuli. I should have a wasted_stimuli table (two column: stimuli and session_id)) with a foreign key to the session_id? – JP Foster Nov 07 '15 at 21:30
  • the concept is there sure. The **details** of it need to be documented and understood – Drew Nov 07 '15 at 21:40
  • Well thanks for the help. I have one more question. I'm reading thru the documentation, and I'm finding where does this stop? Sure I can have a user table, and a tokens table. Where I add in user_tokens with a PK to users_id tokens number. Does this really make a difference? or is it really only applied to things like, 1 user and multiple of another thing? Like we talked about in the wasted_stimuli. Also I'm concerned with when I input the data, wont i need to make numeous queries to populate all associated tables? – JP Foster Nov 07 '15 at 22:25
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/94506/discussion-between-drew-and-jp-foster). – Drew Nov 07 '15 at 22:33