Entry

Database: MySql: Table: Split: How possibly divide 1 large table in smaller subtables? [foreign key]

Jul 6th, 2008 13:24
Knud van Eeden, dman,

----------------------------------------------------------------------
--- Knud van Eeden --- 19 May 2021 - 02:58 pm ------------------------
Database: MySql: Table: Split: How to divide 1 large table in smaller 
subtables? [foreign key]
---
You could possibly divide this large table with many columns in smaller
subtables, by letting this large table be the master table with a
primary key.
Then splitting this larger table in smaller tables, by moving the
desired fields into smaller tables with a foreign key pointing to the
primary key of this larger table.
===
e.g.
Before:
Some large table with many columns
---
--- cut here: begin --------------------------------------------------
CREATE TABLE
 tableFriendDataMainOld
(
 columnFriendDataMainIdI INT AUTO_INCREMENT PRIMARY KEY,
 columnFirstName VARCHAR( 50 ),
 columnLastName VARCHAR( 50 ),
 columnStreet VARCHAR( 100 ),
 columnCity VARCHAR( 100 ),
 columnZipCode VARCHAR( 10 ),
 columnCountry VARCHAR( 30 ),
 columnTelephone VARCHAR( 20 ),
 columnFax VARCHAR( 20 ),
 columnInternetUrl VARCHAR( 50 ),
 columnPicture BLOB,
 columnPictureFilename VARCHAR( 100),
 columnRemark VARCHAR( 255 )
)
;
--- cut here: end ----------------------------------------------------
---
After:
Some less large master table with less columns, and several sub tables
with foreign keys (which point to this earlier larger master table)
---
--- cut here: begin --------------------------------------------------
CREATE TABLE
 tableFriendDataMainNew (
 columnFriendDataMainIdI INT AUTO_INCREMENT PRIMARY KEY,
 columnFirstName VARCHAR( 50 ),
 columnLastName VARCHAR( 50 )
)
TYPE = INNODB
;
--- cut here: end ----------------------------------------------------
---
Some smaller subtable, with some columns, and a foreign key
pointing to the master table.
---
--- cut here: begin --------------------------------------------------
CREATE TABLE
 tableAddressS
(
 columnAddressIdI INT AUTO_INCREMENT PRIMARY KEY,
 columnFriendDataMainIdI INT,
 columnFirstName VARCHAR( 50 ),
 columnLastName VARCHAR( 100 ),
 columnStreet VARCHAR( 100 ),
 columnCity VARCHAR( 100 ),
 columnZipCode VARCHAR( 10 ),
 columnCountry VARCHAR( 30 ),
 columnTelephone VARCHAR( 20 ),
 columnFax VARCHAR( 20 ),
 columnInternetUrl VARCHAR( 50 ),
 INDEX ( columnFriendDataMainIdI ),
 FOREIGN KEY ( columnFriendDataMainIdI ) REFERENCES 
tableFriendDataMainNew ( columnFriendDataMainIdI )
)
TYPE = INNODB
;
--- cut here: end ----------------------------------------------------
---
Some other smaller subtable, with some columns, and a foreign key
pointing to the master table.
---
--- cut here: begin --------------------------------------------------
CREATE TABLE
 tableFriendDataExtra
(
 columnFriendDataExtraIdI INT AUTO_INCREMENT PRIMARY KEY,
 columnFriendDataMainIdI INT,
 columnPicture BLOB,
 columnRemark VARCHAR( 255 ),
 INDEX ( columnFriendDataMainIdI ),
 FOREIGN KEY ( columnFriendDataMainIdI ) REFERENCES 
tableFriendDataMainNew ( columnFriendDataMainIdI )
)
TYPE = INNODB
;
--- cut here: end ----------------------------------------------------
===
Steps: Overview:
 1. -Start MySql
 2. -Create the following SQL code
--- cut here: begin --------------------------------------------------
CREATE DATABASE IF NOT EXISTS
 database1;
USE database1;
--- old large table ---
CREATE TABLE IF NOT EXISTS
 tableFriendDataMainOld
(
 columnFriendDataMainIdI INT AUTO_INCREMENT PRIMARY KEY,
 columnFirstName VARCHAR( 50 ),
 columnLastName VARCHAR( 50 ),
 columnStreet VARCHAR( 100 ),
 columnCity VARCHAR( 100 ),
 columnZipCode VARCHAR( 10 ),
 columnCountry VARCHAR( 30 ),
 columnTelephone VARCHAR( 20 ),
 columnFax VARCHAR( 20 ),
 columnInternetUrl VARCHAR( 50 ),
 columnPicture BLOB,
 columnPictureFilename VARCHAR( 100),
 columnRemark VARCHAR( 255 )
)
;
--- old large table now splitted in smaller parts ---
CREATE TABLE IF NOT EXISTS
 tableFriendDataMainNew (
 columnFriendDataMainIdI INT AUTO_INCREMENT PRIMARY KEY,
 columnFirstName VARCHAR( 50 ),
 columnLastName VARCHAR( 50 )
)
TYPE = INNODB
;
CREATE TABLE IF NOT EXISTS
 tableAddressS
(
 columnAddressIdI INT AUTO_INCREMENT PRIMARY KEY,
 columnFriendDataMainIdI INT,
 columnFirstName VARCHAR( 50 ),
 columnLastName VARCHAR( 100 ),
 columnStreet VARCHAR( 100 ),
 columnCity VARCHAR( 100 ),
 columnZipCode VARCHAR( 10 ),
 columnCountry VARCHAR( 30 ),
 columnTelephone VARCHAR( 20 ),
 columnFax VARCHAR( 20 ),
 columnInternetUrl VARCHAR( 50 ),
 INDEX ( columnFriendDataMainIdI ),
 FOREIGN KEY ( columnFriendDataMainIdI ) REFERENCES 
tableFriendDataMainNew ( columnFriendDataMainIdI )
)
TYPE = INNODB
;
CREATE TABLE IF NOT EXISTS
 tableFriendDataExtra
(
 columnFriendDataExtraIdI INT AUTO_INCREMENT PRIMARY KEY,
 columnFriendDataMainIdI INT,
 columnPicture BLOB,
 columnRemark VARCHAR( 255 ),
 INDEX ( columnFriendDataMainIdI ),
 FOREIGN KEY ( columnFriendDataMainIdI ) REFERENCES 
tableFriendDataMainNew ( columnFriendDataMainIdI )
)
TYPE = INNODB
;
--- cut here: end ----------------------------------------------------
 3. -Run this SQL query
 4. -The result will be now a smaller main table,
     with the removed columns spread over (two)
     smaller subtables.
---
---
Internet: see also:
---
Database: MySQL: Table: Master: Detail: How create master detail table 
MySQL? [foreign key / InnoDB]
http://www.faqts.com/knowledge_base/view.phtml/aid/34396/fid/183
---
Database: MySql: PHPRunner: Table: Split: How to possibly divide 1 
large table in smaller subtables?
http://www.faqts.com/knowledge_base/view.phtml/aid/36333/fid/1805
---
Database: Relational: MySql: Link: Overview: Can you give an overview 
of links?
http://www.faqts.com/knowledge_base/view.phtml/aid/35331/fid/52
----------------------------------------------------------------------