Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Update survey mysql workbench data model #4

Open
ryantanaka opened this issue Sep 27, 2017 · 3 comments
Open

Update survey mysql workbench data model #4

ryantanaka opened this issue Sep 27, 2017 · 3 comments
Assignees
@ryantanaka
Labels
prio:high type: documentation

Comments

@ryantanaka
Copy link

It appears some tables are not following the model represented in the diagram. (ie User and Users tables). Related issue
@ryantanaka
Copy link
Author

ryantanaka commented Oct 10, 2017 •
edited
Loading

for my reference, this is the current status of the kiosk database

kiosk=> \dt
                 List of relations
 Schema |      Name       | Type  |      Owner      
--------+-----------------+-------+-----------------
 public | building        | table | sepgroup_nonmsg
 public | deployed_url    | table | sepgroup_nonmsg
 public | kiosk_survey    | table | sepgroup_nonmsg
 public | option          | table | sepgroup_nonmsg
 public | project         | table | sepgroup_nonmsg
 public | question        | table | sepgroup_nonmsg
 public | response        | table | sepgroup_nonmsg
 public | survey_info     | table | sepgroup_nonmsg
 public | survey_question | table | sepgroup_nonmsg
 public | user            | table | webuser
 public | users           | table | webuser
(11 rows)

kiosk=> \dt building
              List of relations
 Schema |   Name   | Type  |      Owner      
--------+----------+-------+-----------------
 public | building | table | sepgroup_nonmsg
(1 row)

kiosk=> \d building
                                       Table "public.building"
   Column    |         Type          |                           Modifiers                            
-------------+-----------------------+----------------------------------------------------------------
 building_id | integer               | not null default nextval('building_building_id_seq'::regclass)
 name        | character varying(45) | 
Indexes:
    "building_pkey" PRIMARY KEY, btree (building_id)
Referenced by:
    TABLE "deployed_url" CONSTRAINT "deployed_url_building_id_fkey" FOREIGN KEY (building_id) REFERENCES building(building_id)

kiosk=> \d deployed_url
                                            Table "public.deployed_url"
     Column      |          Type          |                               Modifiers                                
-----------------+------------------------+------------------------------------------------------------------------
 deployed_url_id | integer                | not null default nextval('deployed_url_deployed_url_id_seq'::regclass)
 url_text        | character varying(255) | 
 building_id     | integer                | 
 is_kioski       | boolean                | 
Indexes:
    "deployed_url_pkey" PRIMARY KEY, btree (deployed_url_id)
Foreign-key constraints:
    "deployed_url_building_id_fkey" FOREIGN KEY (building_id) REFERENCES building(building_id)
Referenced by:
    TABLE "response" CONSTRAINT "response_deployed_url_id_fkey" FOREIGN KEY (deployed_url_id) REFERENCES deployed_url(deployed_url_id)

kiosk=> \d kiosk_survey
             Table "public.kiosk_survey"
     Column      |         Type          | Modifiers 
-----------------+-----------------------+-----------
 url             | character varying(45) | not null
 survey_info_id  | integer               | not null
 deployed_url_id | integer               | not null
Indexes:
    "kiosk_survey_pkey" PRIMARY KEY, btree (url)

kiosk=> \d option
                                      Table "public.option"
   Column    |         Type          |                         Modifiers                          
-------------+-----------------------+------------------------------------------------------------
 option_id   | integer               | not null default nextval('option_option_id_seq'::regclass)
 question_id | integer               | 
 text        | character varying(45) | 
 value       | character varying(45) | 
Indexes:
    "option_pkey" PRIMARY KEY, btree (option_id)
Foreign-key constraints:
    "option_question_id_fkey" FOREIGN KEY (question_id) REFERENCES question(question_id)
Referenced by:
    TABLE "response" CONSTRAINT "response_option_id_fkey" FOREIGN KEY (option_id) REFERENCES option(option_id)

kiosk=> \d project
                                       Table "public.project"
   Column    |         Type          |                          Modifiers                           
-------------+-----------------------+--------------------------------------------------------------
 project_id  | integer               | not null default nextval('project_project_id_seq'::regclass)
 description | character varying(45) | 
 project_url | character varying(45) | 
Indexes:
    "project_pkey" PRIMARY KEY, btree (project_id)
Referenced by:
    TABLE "survey_info" CONSTRAINT "survey_info_project_id_fkey" FOREIGN KEY (project_id) REFERENCES project(project_id)

kiosk=> \d question
                                            Table "public.question"
        Column        |          Type          |                           Modifiers                            
----------------------+------------------------+----------------------------------------------------------------
 question_id          | integer                | not null default nextval('question_question_id_seq'::regclass)
 question_text        | character varying(150) | 
 question_description | character varying(150) | 
 question_type        | character varying(50)  | 
Indexes:
    "question_pkey" PRIMARY KEY, btree (question_id)
Referenced by:
    TABLE "option" CONSTRAINT "option_question_id_fkey" FOREIGN KEY (question_id) REFERENCES question(question_id)
    TABLE "response" CONSTRAINT "response_question_id_fkey" FOREIGN KEY (question_id) REFERENCES question(question_id)
    TABLE "survey_question" CONSTRAINT "survey_question_question_id_fkey" FOREIGN KEY (question_id) REFERENCES question(question_id)

kiosk=> \d response
                                           Table "public.response"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 response_id     | integer                  | not null default nextval('response_response_id_seq'::regclass)
 survey_info_id  | integer                  | not null
 question_id     | integer                  | not null
 deployed_url_id | integer                  | not null
 option_id       | integer                  | not null
 timestamp       | timestamp with time zone | not null
Indexes:
    "response_pkey" PRIMARY KEY, btree (response_id)
Foreign-key constraints:
    "response_deployed_url_id_fkey" FOREIGN KEY (deployed_url_id) REFERENCES deployed_url(deployed_url_id)
    "response_option_id_fkey" FOREIGN KEY (option_id) REFERENCES option(option_id)
    "response_question_id_fkey" FOREIGN KEY (question_id) REFERENCES question(question_id)
    "response_survey_info_id_fkey" FOREIGN KEY (survey_info_id) REFERENCES survey_info(survey_info_id)

kiosk=> \d response
                                           Table "public.response"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 response_id     | integer                  | not null default nextval('response_response_id_seq'::regclass)
 survey_info_id  | integer                  | not null
 question_id     | integer                  | not null
 deployed_url_id | integer                  | not null
 option_id       | integer                  | not null
 timestamp       | timestamp with time zone | not null
Indexes:
    "response_pkey" PRIMARY KEY, btree (response_id)
Foreign-key constraints:
    "response_deployed_url_id_fkey" FOREIGN KEY (deployed_url_id) REFERENCES deployed_url(deployed_url_id)
    "response_option_id_fkey" FOREIGN KEY (option_id) REFERENCES option(option_id)
    "response_question_id_fkey" FOREIGN KEY (question_id) REFERENCES question(question_id)
    "response_survey_info_id_fkey" FOREIGN KEY (survey_info_id) REFERENCES survey_info(survey_info_id)

kiosk=> \d survey_info
                                          Table "public.survey_info"
     Column     |         Type          |                              Modifiers                               
----------------+-----------------------+----------------------------------------------------------------------
 survey_info_id | integer               | not null default nextval('survey_info_survey_info_id_seq'::regclass)
 survey_name    | character varying(45) | not null
 description    | character varying(45) | 
 project_id     | integer               | 
Indexes:
    "survey_info_pkey" PRIMARY KEY, btree (survey_info_id)
    "survey_info_survey_name_key" UNIQUE CONSTRAINT, btree (survey_name)
Foreign-key constraints:
    "survey_info_project_id_fkey" FOREIGN KEY (project_id) REFERENCES project(project_id)
Referenced by:
    TABLE "response" CONSTRAINT "response_survey_info_id_fkey" FOREIGN KEY (survey_info_id) REFERENCES survey_info(survey_info_id)
    TABLE "survey_question" CONSTRAINT "survey_question_survey_info_id_fkey" FOREIGN KEY (survey_info_id) REFERENCES survey_info(survey_info_id)

kiosk=> \d survey_question
     Table "public.survey_question"
      Column       |  Type   | Modifiers 
-------------------+---------+-----------
 survey_info_id    | integer | not null
 question_id       | integer | not null
 question_position | integer | 
Indexes:
    "survey_question_pkey" PRIMARY KEY, btree (survey_info_id, question_id)
Foreign-key constraints:
    "survey_question_question_id_fkey" FOREIGN KEY (question_id) REFERENCES question(question_id)
    "survey_question_survey_info_id_fkey" FOREIGN KEY (survey_info_id) REFERENCES survey_info(survey_info_id)

kiosk=> \d user
                                       Table "public.user"
    Column     |          Type          |                       Modifiers                        
---------------+------------------------+--------------------------------------------------------
 user_id       | integer                | not null default nextval('user_user_id_seq'::regclass)
 username      | character varying(32)  | 
 password_hash | character varying(128) | 
Indexes:
    "user_pkey" PRIMARY KEY, btree (user_id)
    "ix_user_username" btree (username)

kiosk=> \d users
                                       Table "public.users"
    Column     |          Type          |                        Modifiers                        
---------------+------------------------+---------------------------------------------------------
 user_id       | integer                | not null default nextval('users_user_id_seq'::regclass)
 username      | character varying(32)  | 
 password_hash | character varying(128) | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "ix_users_username" btree (username)

@ryantanaka
Copy link
Author

for my reference, (ER diagram pulled from navicat)

ERD of kiosk schema

kiosk_current_schema

@carlosparadis
Copy link
Member

@ryantanaka That reminds me: When you organize the tables, position them and the bigger blocks using the data model in the database as a reference. This facilitates comparison in the long run, if the relative positions are the same (i.e. kiosk block and it's tables are on the top right, but also take note of how they are organized spatially inside the block).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ryantanaka ryantanaka

Labels
prio:high type: documentation
Milestone
No milestone
Development

No branches or pull requests
2 participants
@carlosparadis @ryantanaka