 | |
#1
 
| |||
| |||
|
Insert vs Alter? -
01-24-2011
, 11:15 AM
#2
| |||
| |||
|
|
I'm a newbie when it comes to SQL... I'll try to explain what I'm doing by an example I found on the net. I once had a table like: myTable: Last_Name, First_Name, Order_ID, Order_Date, Amount. I split that table into two tables: Customer Table, Primary Key is SID SID, Last_Name, First_Name Orders Table, Primary Key is Order_ID, Foreign Key is Customer_SID Order_ID, Order_Date, Amount, Customer_SID Before I split the table into two insertion into the table was simple. INSERT INTO myTable (Last_Name, First_Name, Order_ID, Order_Date, Amount) VALUES ('john', 'doe', '1', '20110101', 100.00); Now that I have two tables what is the proper way of added a record to the database? |
instead of one. You just need to replace "INSERT INTO myTable" with
"INSERT INTO Customer" and so on...
Sorry, but it's hard to understand your exact doubt... :-?
Quote:
|
P.S. I have mysql workbench installed on my mac and I noticed a forum there... Would it be appropriate to ask this question there? |
Quote:
|
P.P.S. Would the workbench generate insert statements for me? |
Statement". But you still have to type the values
Er... Are you storing orders in the database by typing yourself all the
data? Don't you have a, well, a program?
--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
#3
| |||||
| |||||
|
|
I once had a table like: myTable: Last_Name, First_Name, Order_ID, Order_Date, Amount. |
Quote:
|
I split that table into two tables: Customer Table, Primary Key is SID SID, Last_Name, First_Name Orders Table, Primary Key is Order_ID, Foreign Key is Customer_SID Order_ID, Order_Date, Amount, Customer_SID |
Now, you have each Customer just once, and each Customer can have
multiple Orders.
Quote:
|
Before I split the table into two insertion into the table was simple. INSERT INTO myTable (Last_Name, First_Name, Order_ID, Order_Date, Amount) VALUES ('john', 'doe', '1', '20110101', 100.00); Now that I have two tables what is the proper way of added a record to the database? |
You're probably not where you want to be, yet. In order to find out,
you must first set the concept of tables aside, and think in terms of
'entities'.
What are you trying to administrate?
- you have Customers
- and Orders
- and a relationship: "each Customer has at least one Order, or he has
many Orders. There are no Orders without exactly one Customer." (is
that correct?)
Now, WHAT is being ordered? An Article? Perhaps multiple Articles?
Anything else?
If so, don't you want to model that? For instance:
Customers : Orders = 1:n
Orders : Articles = n:m
If the latter is the case, you need to normalize. In that case, it will
probably look like:
Orders : OrderPosition : Article = 1:m:1
Your next step would be to define the nature of the relationships. Do
you have Orders with zero OrderPositions? Have all Articles been
ordered at least once? What happens to Orders, if an Article is
removed, that was linked to an Order through OrderPosition?
As soon as you have defined such entities and relationships, you're
ready to think about table structures.
Quote:
|
P.S. I have mysql workbench installed on my mac and I noticed a forum there... Would it be appropriate to ask this question there? |
Quote:
|
P.P.S. Would the workbench generate insert statements for me? |
Wouldn't that be a bit of an uncontrolled environment?
--
Erick
#4
| |||
| |||
|
|
SpreadTooThin: I once had a table like: myTable: Last_Name, First_Name, Order_ID, Order_Date, Amount. So, you had a Person who Ordered. Each Order belonged to one Person. I split that table into two tables: Customer Table, Primary Key is SID SID, Last_Name, First_Name Orders Table, Primary Key is Order_ID, Foreign Key is Customer_SID Order_ID, Order_Date, Amount, Customer_SID Good start. Now, you have each Customer just once, and each Customer can have multiple Orders. Before I split the table into two insertion into the table was simple. INSERT INTO myTable (Last_Name, First_Name, Order_ID, Order_Date, Amount) VALUES ('john', 'doe', '1', '20110101', 100.00); Now that I have two tables what is the proper way of added a record to the database? Too early! :-) You're probably not where you want to be, yet. In order to find out, you must first set the concept of tables aside, and think in terms of 'entities'. What are you trying to administrate? - you have Customers - and Orders - and a relationship: "each Customer has at least one Order, or he has many Orders. There are no Orders without exactly one Customer." (is that correct?) Now, WHAT is being ordered? An Article? Perhaps multiple Articles? Anything else? If so, don't you want to model that? For instance: Customers : Orders = 1:n Orders : Articles = n:m If the latter is the case, you need to normalize. In that case, it will probably look like: Orders : OrderPosition : Article = 1:m:1 Your next step would be to define the nature of the relationships. Do you have Orders with zero OrderPositions? Have all Articles been ordered at least once? What happens to Orders, if an Article is removed, that was linked to an Order through OrderPosition? As soon as you have defined such entities and relationships, you're ready to think about table structures. P.S. *I have mysql workbench installed on my mac and I noticed a forum there... Would it be appropriate to ask this question there? Well, if you are referring to forums.mysql.com, then certainly. P.P.S. Would the workbench generate insert statements for me? Do you want to do live data entry in a database development tool? Wouldn't that be a bit of an uncontrolled environment? -- Erick |
have none or many orders...
Now that that is said....
So I insert first into the customer table (and assuming uniqueness
that should be easy)
but I'm confused as to the foreign key and making sure that is handled
properly...
So what do the insert statements look like?
#5
| |||
| |||
|
|
On Jan 24, 10:37*am, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m wrote: SpreadTooThin: I once had a table like: myTable: Last_Name, First_Name, Order_ID, Order_Date, Amount. So, you had a Person who Ordered. Each Order belonged to one Person. I split that table into two tables: Customer Table, Primary Key is SID SID, Last_Name, First_Name Orders Table, Primary Key is Order_ID, Foreign Key is Customer_SID Order_ID, Order_Date, Amount, Customer_SID Good start. Now, you have each Customer just once, and each Customer can have multiple Orders. Before I split the table into two insertion into the table was simple. INSERT INTO myTable (Last_Name, First_Name, Order_ID, Order_Date, Amount) VALUES ('john', 'doe', '1', '20110101', 100.00); Now that I have two tables what is the proper way of added a record to the database? Too early! :-) You're probably not where you want to be, yet. In order to find out, you must first set the concept of tables aside, and think in terms of 'entities'. What are you trying to administrate? - you have Customers - and Orders - and a relationship: "each Customer has at least one Order, or he has many Orders. There are no Orders without exactly one Customer." (is that correct?) Now, WHAT is being ordered? An Article? Perhaps multiple Articles? Anything else? If so, don't you want to model that? For instance: Customers : Orders = 1:n Orders : Articles = n:m If the latter is the case, you need to normalize. In that case, it will probably look like: Orders : OrderPosition : Article = 1:m:1 Your next step would be to define the nature of the relationships. Do you have Orders with zero OrderPositions? Have all Articles been ordered at least once? What happens to Orders, if an Article is removed, that was linked to an Order through OrderPosition? As soon as you have defined such entities and relationships, you're ready to think about table structures. P.S. *I have mysql workbench installed on my mac and I noticed a forum there... Would it be appropriate to ask this question there? Well, if you are referring to forums.mysql.com, then certainly. P.P.S. Would the workbench generate insert statements for me? Do you want to do live data entry in a database development tool? Wouldn't that be a bit of an uncontrolled environment? -- Erick Agreed I should have mentioned that for this example, A customer can have none or many orders... Now that that is said.... So I insert first into the customer table (and assuming uniqueness that should be easy) but I'm confused as to the foreign key and making sure that is handled properly... So what do the insert statements look like? |
'certain' things can happen to his orders.. I.e. they are all deleted
or they are set to null etc.. but really I'm just trying to focus on
the INSERT statement that is now totally different.
#6
| |||
| |||
|
|
Agreed I should have mentioned that for this example, A customer can have none or many orders... Now that that is said.... So I insert first into the customer table (and assuming uniqueness that should be easy) but I'm confused as to the foreign key and making sure that is handled properly... So what do the insert statements look like? The table creation manages the fact that if a customer is deleted then 'certain' things can happen to his orders.. I.e. they are all deleted or they are set to null etc.. but really I'm just trying to focus on the INSERT statement that is now totally different. |
why does it not work? Also, can you provide create table statements for
the two tables in question?
/Lennart
#7
| |||
| |||
|
|
On Jan 24, 11:03*am, SpreadTooThin <bjobrie... (AT) gmail (DOT) com> wrote: On Jan 24, 10:37*am, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m wrote: SpreadTooThin: I once had a table like: myTable: Last_Name, First_Name, Order_ID, Order_Date, Amount. So, you had a Person who Ordered. Each Order belonged to one Person. I split that table into two tables: Customer Table, Primary Key is SID SID, Last_Name, First_Name Orders Table, Primary Key is Order_ID, Foreign Key is Customer_SID Order_ID, Order_Date, Amount, Customer_SID Good start. Now, you have each Customer just once, and each Customer can have multiple Orders. Before I split the table into two insertion into the table was simple. INSERT INTO myTable (Last_Name, First_Name, Order_ID, Order_Date, Amount) VALUES ('john', 'doe', '1', '20110101', 100.00); Now that I have two tables what is the proper way of added a record to the database? Too early! :-) You're probably not where you want to be, yet. In order to find out, you must first set the concept of tables aside, and think in terms of 'entities'. What are you trying to administrate? - you have Customers - and Orders - and a relationship: "each Customer has at least one Order, or he has many Orders. There are no Orders without exactly one Customer." (is that correct?) Now, WHAT is being ordered? An Article? Perhaps multiple Articles? Anything else? If so, don't you want to model that? For instance: Customers : Orders = 1:n Orders : Articles = n:m If the latter is the case, you need to normalize. In that case, it will probably look like: Orders : OrderPosition : Article = 1:m:1 Your next step would be to define the nature of the relationships. Do you have Orders with zero OrderPositions? Have all Articles been ordered at least once? What happens to Orders, if an Article is removed, that was linked to an Order through OrderPosition? As soon as you have defined such entities and relationships, you're ready to think about table structures. P.S. *I have mysql workbench installed on my mac and I noticed a forum there... Would it be appropriate to ask this question there? Well, if you are referring to forums.mysql.com, then certainly. P.P.S. Would the workbench generate insert statements for me? Do you want to do live data entry in a database development tool? Wouldn't that be a bit of an uncontrolled environment? -- Erick Agreed I should have mentioned that for this example, A customer can have none or many orders... Now that that is said.... So I insert first into the customer table (and assuming uniqueness that should be easy) but I'm confused as to the foreign key and making sure that is handled properly... So what do the insert statements look like? The table creation manages the fact that if a customer is deleted then 'certain' things can happen to his orders.. I.e. they are all deleted or they are set to null etc.. but really I'm just trying to focus on the INSERT statement that is now totally different. |
The title is "Insert vs Alter?", but these are 2 completely different
things. Insert deals with adding rows to a table whereas Alter deals
with changing the structure of a table.
Also you say that "the INSERT statement [that] is now totally
different.". But the insert statement you will now use will be almost
exactly the same as the one you used before.
So just what are you actually trying to ask about?
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.