1

It's been quite a few years since I have done DB work. I am working through mysql workbench (using MS Access as a frontend).

I have a base table that I am multilinking (basically a name index type of thing). Through a form, the table is updated (a record added) and I want that new record to trigger an event that creates a table and that table is named based on what the user inputted in the form/record.

Any thoughts on how to do this? Good tutorial/documentation that you would recommend reading? Thanks!

  • Yeah I have a habit of just saying either mysql or workbench - figured I would say workbench so that you had an understanding of the interface I am using. Each new table will be customized based on x months - in which data is put in for each month for anywhere from 30 to 500 records. Sometimes it will be as little as 6 months and other times it will be as large as 48months. Instead of creating a table that will have to grow to encompass 48 months and try to force everything to play nice, I am thinking that its better all around to seperate tables (perf, organizationally, etc). – Mike Jun 12 at 16:51
1

First things first

this would you use, to create special table per id Name and table you have to add

DELIMITER $$

CREATE TRIGGER after_table_insert
AFTER INSERT
ON tabl1 FOR EACH ROW
BEGIN
    SET @sql = CONCAR("CREATE TABLE table_",NEW.name,"_",NEW.ID, " (id INT,name TEXT)");
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$

DELIMITER ;

If you had a table for all

CREATE TABLE Table1_companion
( id BIGINT AUTO_INCREMENT PRIMARY KEy
, Refid  INTEGER
,col1 TEXT
,col2 TEXT
,FOREIGN KEY (Refid) REFERENCES tavle1(id));

So you have

SELECT t1.*,t2.* FROM table1 t1 INNER JOIN Table1_companion t2 ON t1.id = t2.Refid;

And when you have a n:m Relationship

You need two tables

CREATE TABLE Table1_companion
( tc_id BIGINT AUTO_INCREMENT PRIMARY KEy
,col1 TEXT
,col2 TEXT);

CREATE TABLE Table1_relation
(
, Ref_id  INTEGER
, Ref_tc_id   BIGINT
,FOREIGN KEY (Ref_id) REFERENCES table1(id)
,FOREIGN KEY (Ref_tc_id) REFERENCES Table1_companion(tc_id ));

Of course you need two joins for that.

You could take a look at normalization.

With that you have a relational database, that works with every rdms.

this is of course only a basic idea, you must have your own datatypes and foreign keys need of both sides the same datatype, and always remember have every information only once in your database

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.