search

erdl / database

Data model template for previous and current projects, and issues associated to the server database.
0 stars 1 forks source link

Update survey mysql workbench data model #4

Open ryantanaka opened 6 years ago

ryantanaka commented 6 years ago

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

ryantanaka commented 6 years ago

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 commented 6 years ago

for my reference, (ER diagram pulled from navicat)

ERD of kiosk schema

kiosk_current_schema

carlosparadis commented 6 years ago

@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).