From single table to relational.

#1

If I have a database that has a table that needs to be split into two
tables, is there a simple way to write a script to convert it from one
schema to another?

Let me explain by example...
Here the entire data set is held in one table. (poor design choice.)

Table1
Column1 Primary Key (Unique)
Column2
Column3
Column4

I'm told this should be done with a multiple tables using foreign
keys:
So..

Table1
Column1 Primary Key
Column2

Table2
Column3 New Primary Key
Foreign_Key '(link to table 1 Column1)
Column4

So if I understand this correctly, the foreign key in table2 points to
an entry of table1 column1 and now we can have an index for Column3..

I understand that this multi-table (Or relational) schema is a
'better' design and will take less space an make queries run faster.

Have I understood this?

crowfoot notation...
There is a 1 to many relationship between table1 and table2.

What happens when a row from table1 is deleted? Do all entries in
Table2 get automatically deleted somehow?

#2

On 20-11-10 04:09, SpreadTooThin wrote:

Quote:

If I have a database that has a table that needs to be split into two
tables, is there a simple way to write a script to convert it from one
schema to another?

Let me explain by example...
Here the entire data set is held in one table. (poor design choice.)

Table1
Column1 Primary Key (Unique)
Column2
Column3
Column4

I'm told this should be done with a multiple tables using foreign
keys:
So..

Table1
Column1 Primary Key
Column2

Table2
Column3 New Primary Key
Foreign_Key '(link to table 1 Column1)
Column4

So if I understand this correctly, the foreign key in table2 points to
an entry of table1 column1 and now we can have an index for Column3..

I understand that this multi-table (Or relational) schema is a
'better' design and will take less space an make queries run faster.

Have I understood this?

crowfoot notation...
There is a 1 to many relationship between table1 and table2.

What happens when a row from table1 is deleted? Do all entries in
Table2 get automatically deleted somehow?

http://dev.mysql.com/doc/refman/5.1/...nstraints.html

--
Luuk

#3

On 2010-11-20 04:09, SpreadTooThin wrote:
[...]

Quote:

So if I understand this correctly, the foreign key in table2 points to
an entry of table1 column1 and now we can have an index for Column3..

I understand that this multi-table (Or relational) schema is a
'better' design and will take less space an make queries run faster.

Have I understood this?

The main purpose of normalization is to avoid update anomalies. You can
find some info on:

http://en.wikipedia.org/wiki/Database_normalization

/Lennart

#4

I'm using mysql workbench.
I've defined two tables and I'm trying to link them together.
I know this may sound newbie-ish.. but...

After selecting a 1:1 relationship...

I click on Table 1 Column 1 then click on Table 2 Foreign_Key...
but it seems to want to add a column for me... Should I not have
defined the Foreign_Key in Table2 myself?
Does the EER Diagram Editor want you to click on the columns involved
in the relationship?

would it be possible to send me a sample mySQLWorkbench project to
bobrien 18 at yahoo . com

#5

SpreadTooThin:

Quote:

I'm using mysql workbench.
After selecting a 1:1 relationship...

Why not the 1:n relationship?

Quote:

I click on Table 1 Column 1 then click on Table 2 Foreign_Key...
but it seems to want to add a column for me...

....which is the foreign key.

Quote:

Should I not have defined the Foreign_Key in Table2 myself?

Exactly. You click a 1:n relationship and the Workbench adds the
primary key of one side as a foreign key field to the other side.

Quote:

Does the EER Diagram Editor want you to click on the columns involved
in the relationship?

There are no columns involved. It takes the primary key.

--
Erick

#6

On Nov 22, 1:07*pm, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:

Quote:

SpreadTooThin:

I'm using mysql workbench.
After selecting a 1:1 relationship...

Why not the 1:n relationship?

I click on Table 1 Column 1 then click on Table 2 Foreign_Key...
but it seems to want to add a column for me...

...which is the foreign key.

Should I not have defined the Foreign_Key in Table2 myself?

Exactly. You click a 1:n relationship and the Workbench adds the
primary key of one side as a foreign key field to the other side.

Does the EER Diagram Editor want you to click on the columns involved
in the relationship?

There are no columns involved. It takes the primary key.

--
Erick

Its seems to matter which table you select first too doesn't it?

#7

On 11/22/2010 4:19 PM, SpreadTooThin wrote:

Quote:

On Nov 22, 1:07 pm, "Erick T. Barkhuis"<erick.use-... (AT) ardane (DOT) c.o.m
wrote:
SpreadTooThin:

I'm using mysql workbench.
After selecting a 1:1 relationship...

Why not the 1:n relationship?

I click on Table 1 Column 1 then click on Table 2 Foreign_Key...
but it seems to want to add a column for me...

...which is the foreign key.

Should I not have defined the Foreign_Key in Table2 myself?

Exactly. You click a 1:n relationship and the Workbench adds the
primary key of one side as a foreign key field to the other side.

Does the EER Diagram Editor want you to click on the columns involved
in the relationship?

There are no columns involved. It takes the primary key.

--
Erick

Its seems to matter which table you select first too doesn't it?

Yes, just like it makes a difference if you say 5-3 or 3-5.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#8

On Nov 22, 5:09*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

On 11/22/2010 4:19 PM, SpreadTooThin wrote:

On Nov 22, 1:07 pm, "Erick T. Barkhuis"<erick.use-... (AT) ardane (DOT) c.o.m
wrote:
SpreadTooThin:

I'm using mysql workbench.
After selecting a 1:1 relationship...

Why not the 1:n relationship?

I click on Table 1 Column 1 then click on Table 2 Foreign_Key...
but it seems to want to add a column for me...

...which is the foreign key.

Should I not have defined the Foreign_Key in Table2 myself?

Exactly. You click a 1:n relationship and the Workbench adds the
primary key of one side as a foreign key field to the other side.

Does the EER Diagram Editor want you to click on the columns involved
in the relationship?

There are no columns involved. It takes the primary key.

--
Erick

Its seems to matter which table you select first too doesn't it?

Yes, just like it makes a difference if you say 5-3 or 3-5.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Of course it does.. but...
If I have a table of clients and a table of orders
a client can have many orders.

So do I click on the client first and then the orders second?

#9

SpreadTooThin:

Quote:

If I have a table of clients and a table of orders
a client can have many orders.

So do I click on the client first and then the orders second?

Yes. Here's a test:
- open Workbench
- add a new Diagram
- in that Diagram, click 'new table' and place it on the canvas
(result: 'table1')
- repeat. Result: 'table2'
- double click each table, add a PK column for both
- click 1:n relationship
- click 'table2', then 'table1'. Result: a relationship is drawn, with
a crow foot on the table2-side
- and look: table2 now contains an additional field, which is the FK

table1 is your Customers table, table2 is your Orders table.

PS. Are you sure Orders can't be shared by multiple Customers?

--
Erick