0

Let me give you a bit context first, before I list down my questions.
I am using python, tkinter and MySQL for my project. I have 2 tables in my database timetablebtn whose fields are:

----------------------------------------------------------
Field     | Type          | Null | Key | Default | Extra |
----------+---------------+------+-----+---------+--------
sno       | int(5)        | NO   | PRI |   0     |       |
btnobject | varchar(50)   | NO   |     |  NULL   |       |
tabledate | varchar(20)   | NO   |     |  NULL   |       |
----------------------------------------------------------

An example record for the above would be: 1,"btnobject1","01/01/21", 2,"btnobject2","02/01/21
which I would achieve using the following sql command
INSERT INTO timetablebtn VALUES (1,"btnobject1","01/01/21");
and there are 24 such records in this table.
and timtableevents :

------------------------------------------------------
Field  | Type         | Null | Key | Default | Extra |
-------+--------------+------+-----+---------+-------+
sno    | int(5)       | NO   | PRI |   0     |       |  
time   | varchar(20)  | NO   |     |  NULL   |       |
events | varchar(100) | NO   |     |  NULL   |       |
------------------------------------------------------

An example of this record would be: 1,"07:30-08:30","Exercise"
What I want is that each btnobject in the timetablebtn table be referencing to a different instance of timetablevents,i.e. I want each btnobject to have their own timetablevents without me manually creating each table as creating 24 tables is a lot of work.
OR
I want btnobject to be a foreign key where the structure of a new table would be:

---------------------------------------------
btnobject  | sno | time        | events     |
-----------+-----+-------------+------------+
btnobject1 | 1   | 07:30-08:30 | Exercise   |
           | 2   | 08:30-09:00 | Breakfast  |
           | 3   | 09:00-11:00 | HackerRank |
btnobject2 | 1   | 07:30-08:30 | Exercise   |
           | 2   | 08:30-09:00 | Breakfast  |
           | 3   | 09:00-11:00 | Read books |
---------------------------------------------

OR
Create a table with btnobject as a foreign key where table structure would be:

--------------------
btnobject  | details
-----------+--------
btnobject1 | {'sno':[1,2,3],'time':['07:30-08:30','08:30-09:00','09:00-11:00'],'events':['Exercise','Breakfast','HackerRank'] }
btnobject2 | {'sno':[1,2,3],'time':['07:30-08:30','08:30-09:00','09:00-11:00'],'events':['Exercise','Breakfast','Read books'] }

where the elements under the details column is a python dictionary.
Is there any way to achieve this?
OR
If there is a better way than what I have listed above, then Please do share it with me. Thanks in advance.

Veeraja Veeraesh
  • 194
  • 10
  • Since `sno` is the primary key, you cannot have same value for different btnobject. – acw1668 Sep 28 '21 at 07:49
  • @acw1668 ok what if i make btnobject as the primary key, then will it be possible to store it in the format i have shown above? – Veeraja Veeraesh Sep 28 '21 at 07:50
  • *whose fields are* Please provide CREATE TABLE, not DESCRIBE. *An example record for the above would be* Provide it as INSERT INTO. And latter output examples must match previous source data - so provide needed rows amount. – Akina Sep 28 '21 at 07:52
  • I would suggest that all the fields in `timetablebtn` table are PRIMARY keys and `sno` and `time` are the PRIMARY keys of `timetableevents` table. – acw1668 Sep 28 '21 at 10:12
  • @acw1668, there can only be 1 primary key in one table. But i guess you meant "Foreign Keys"? – Veeraja Veeraesh Sep 28 '21 at 13:18
  • You can have more than one field as the primary keys in MySQL. Foreign key is another thing. – acw1668 Sep 28 '21 at 13:20
  • For example: `CREATE TABLE timetablebtn (sno int NOT NULL AUTO_INCREMENT, btnobject varchar(20) NOT NULL, tabledate varchar(10) NOT NULL, PRIMARY KEY (sno,btnobject,tabledate))`. – acw1668 Sep 28 '21 at 13:29
  • Foreign key example: `CREATE TABLE timetableevents (sno int NOT NULL, time varchar(20) NOT NULL, event varchar(100), PRIMARY KEY (sno,time), CONSTRAINT timetableevents_fk_1 FOREIGN KEY (sno) REFERENCES timetablebtn (sno))`. – acw1668 Sep 28 '21 at 13:37
  • Oh, okay so you meant a COMPOSITE PRIMARY KEY as in this question: [link](https://stackoverflow.com/questions/217945/can-i-have-multiple-primary-keys-in-a-single-table). Anyhow, I figured it out. I decided to go with the option where I stored stuff as a dictionary. Reference: [link](https://stackoverflow.com/questions/19703091/how-to-store-python-dictionary-in-to-mysql-db-through-python) – Veeraja Veeraesh Sep 28 '21 at 14:19
  • I would suggest a *view* instead of dictionary content in a field as it is more easier and convenient on searching. – acw1668 Sep 28 '21 at 14:36
  • Yes, I agree with you, accessing an element inside the dictionary is troublesome and the code becomes messy( in the sense that it is not readable). Although I am not aware of what a _view_ is. Could you link any references? – Veeraja Veeraesh Sep 28 '21 at 14:45
  • A view is simply said the result set of a SELECT statement. – acw1668 Sep 28 '21 at 15:18
  • For example: `CREATE VIEW view1 AS SELECT btnobject, time, event FROM timetablebtn a, timetableevents b WHERE a.sno = b.sno ORDER BY 1, 2`. Then `SELECT * FROM view1` will give result similar to first table result in your question. – acw1668 Sep 28 '21 at 15:28

1 Answers1

0

create new table for match purpose.schema

Kim Dequilla
  • 9
  • 3