This is my example scenario.
I want to insert into this database.  My record comes across having
both the customer info and the order info, one record at a time.  My
question is:

Given that:
 There is are 0 to n orders for a customer.
 A customer record can not be deleted until all their orders are
deleted.

Do I insert the customer data into the customer table first...
   I assume it is alright for that to fail as the customer is unique
and might be being added for the second time.
   I assume  the primary key be created for me by the auto increment

Have I got the Foreign Key setup properly?  It should point to
customer.ID
What does the insert statement look like for this record?

ENGINE=InnoDB;  I guess this is what MySQL prefers.  I don't know if
I've handled Index files correctly...

I hope this question is phrased better this time...




Here is the table creation sql...
CREATE SCHEMA MyTest;
USE MyTest;

CREATE  TABLE IF NOT EXISTS `test`.`customer` (
  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `Cust_Name` VARCHAR(32) NOT NULL ,
  PRIMARY KEY (`ID`) ,
  UNIQUE INDEX `cust_id_UNIQUE` (`ID` ASC) )
ENGINE = InnoDB;


--
-----------------------------------------------------
-- Table
`test`.`orders`
--
-----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test`.`orders` (
  `Order_ID` INT NOT NULL ,
  `Customer_ID` INT NULL ,
  `Order_Date` DATE NULL ,
  `Amount` DOUBLE NULL ,
  PRIMARY KEY (`Order_ID`) ,
  INDEX `ID` () ,
  CONSTRAINT `ID`
    FOREIGN KEY ()
    REFERENCES `test`.`customer` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

On 27-01-11 18:45, SpreadTooThin wrote:
> CREATE  TABLE IF NOT EXISTS `test`.`orders` (
>   `Order_ID` INT NOT NULL ,
>   `Customer_ID` INT NULL ,
>   `Order_Date` DATE NULL ,
>   `Amount` DOUBLE NULL ,
>   PRIMARY KEY (`Order_ID`) ,
>   INDEX `ID` () ,

^^ above line is not correct

>   CONSTRAINT `ID`
>     FOREIGN KEY ()

^^ above line is not correct

>     REFERENCES `test`.`customer` ()
>     ON DELETE NO ACTION
>     ON UPDATE NO ACTION)
> ENGINE = InnoDB;

try to read the docs again on CREATE TABLE, and on FOREIGN KEYS

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

(both link are for 5.0, but in this case, nothing much changed)

-- 
Luuk

On 27-01-11 18:45, SpreadTooThin wrote:
> This is my example scenario.
> I want to insert into this database.  My record comes across having
> both the customer info and the order info, one record at a time.  My
> question is:
> 
> Given that:
>  There is are 0 to n orders for a customer.
>  A customer record can not be deleted until all their orders are
> deleted.
> 
> Do I insert the customer data into the customer table first...
>    I assume it is alright for that to fail as the customer is unique
> and might be being added for the second time.
>    I assume  the primary key be created for me by the auto increment

Ye, you do insert the customer first, but only if it does not exists!
There is no reason for storing a customer more than once, is there?


> 
> Have I got the Foreign Key setup properly?  It should point to
> customer.ID
> What does the insert statement look like for this record?

The INSERT looks just like a normal INSERT

INSERT into `customer` (<fields>) VALUES (<values>) ;

> 
> ENGINE=InnoDB;  I guess this is what MySQL prefers.  I don't know if
> I've handled Index files correctly...
> 
> I hope this question is phrased better this time...
> 
> 
> 
> 
> Here is the table creation sql...
....


-- 
Luuk

On 1/27/2011 12:45 PM, SpreadTooThin wrote:
> This is my example scenario.
> I want to insert into this database.  My record comes across having
> both the customer info and the order info, one record at a time.  My
> question is:
>
> Given that:
>   There is are 0 to n orders for a customer.
>   A customer record can not be deleted until all their orders are
> deleted.
>

OK, that's pretty common

> Do I insert the customer data into the customer table first...
>     I assume it is alright for that to fail as the customer is unique
> and might be being added for the second time.

In such a scenario, you have to have a customer table present before you 
can enter an order.  Otherwise you have no customer to attach the order to.

However, they are not necessarily added at the same time, and you 
shouldn't get duplicates for the customer.  For instance, when a person 
registers on the system, that would create a customer which could be 
used for multiple orders.  They may or may not have any orders in the 
system, but they are registered as a customer.

On the other end, if a person chooses not to register, they would get a 
customer entry at checkout time.  They could chose to make this a 
permanent customer entry, i.e. by supplying registration information 
like passwords, etc., or a temporary customer only for this order.

But if they attempt to create a second customer with the same 
information as the first i.e. same user id, same email address, 
whatever), then they would get an error message to that effect instead 
of a duplicate row being created.

But in no case should you have duplicate customers, and attempts to add 
a duplicate should not be ignored.

>     I assume  the primary key be created for me by the auto increment
>

That's a good way to do it.

> Have I got the Foreign Key setup properly?  It should point to
> customer.ID

See below

> What does the insert statement look like for this record?
>

The same as it would look for any table.  Whether or not you have a 
FOREIGN KEY does not affect INSERT statement syntax.  It just adds extra 
constraints to the values in a column.

> ENGINE=InnoDB;  I guess this is what MySQL prefers.  I don't know if
> I've handled Index files correctly...
>

If you want foreign key constraints to work, yes.  MyISAM supports the 
syntax but ignores the actual relationship.

> I hope this question is phrased better this time...
>
>
>
>
> Here is the table creation sql...
> CREATE SCHEMA MyTest;
> USE MyTest;
>
> CREATE  TABLE IF NOT EXISTS `test`.`customer` (
>    `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
>    `Cust_Name` VARCHAR(32) NOT NULL ,
>    PRIMARY KEY (`ID`) ,
>    UNIQUE INDEX `cust_id_UNIQUE` (`ID` ASC) )
> ENGINE = InnoDB;
>

I would assume you would have more information than just cust_name in 
here.  Things like userid (which may or may not be the customer's name), 
email (might be the userid), password, etc.

Also, you don't need a index on ID - the fact it is the primary key 
automatically creates a unique index on the column.

Also, drop the `test` - if you want to create in the test database, USE 
the database first.  Then your SQL is easily transportable to a 
production table.

One other thing - I detest using the backticks - they are a MySQLism, 
and only required if you use reserved words in your table or column 
names.  Among other things, the statements are not transportable to 
another database.  But then it's better not to use reserved words 
anyway, in which case the backticks just add a layer of confusion.

However, this also looks like it was at least in part created by a tool 
(PHPMyAdmin?), in which case I understand all the extra stuff.

>
> --
> -----------------------------------------------------
> -- Table
> `test`.`orders`
> --
> -----------------------------------------------------
> CREATE  TABLE IF NOT EXISTS `test`.`orders` (
>    `Order_ID` INT NOT NULL ,
>    `Customer_ID` INT NULL ,
>    `Order_Date` DATE NULL ,
>    `Amount` DOUBLE NULL ,
>    PRIMARY KEY (`Order_ID`) ,
>    INDEX `ID` () ,
>    CONSTRAINT `ID`
>      FOREIGN KEY ()
>      REFERENCES `test`.`customer` ()
>      ON DELETE NO ACTION
>      ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
>

First of all, I'm assuming you're going to want the order id to be auto 
increment also, so you don't have to generate a new id each time though.

What do you want to index on?  Customer_ID?  If so, you should have:

   INDEX ID (Customer_ID)

On your CONSTRAINT, you can specify an index, but I prefer not to. 
MySQL will still use an index if it can, but it can choose whether or 
not to use the index.  Here you are forcing it to use an index which may 
be suboptimal (it isn't in this case, but not necessarily a good habit 
to get into).

On your CONSTRAINT you also need to specify the columns, i.e.

     FOREIGN KEY (Customer_ID)
     REFERENCES customer(ID)

Also, while you can use NO ACTION, I prefer RESTRICT for two reasons: 
both will cause the DELETE or UPDATE to fail if it causes a foreign key 
constraint violation.  However, NO ACTION is a delayed check while 
RESTRICT is an immediate check.  There are advantages and disadvantages 
to both (which I won't get into here as it's not pertinent), but 
generally I prefer RESTRICT).

In MySQL, both cause an immediate check (no delayed check), so there's 
no difference, except that I think RESTRICT is easier to understand and 
closer to what happens.

So my final version would look something like:

USE TEST;

CREATE TABLE IF NOT EXISTS customer (
   ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   Cust_Name VARCHAR(32) NOT NULL,
   PRIMARY KEY (ID)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS orders (
   Order_ID INT NOT NULL AUTO_INCREMENT,
   Customer_ID INT NULL,
   Order_Date DATE NULL,
   Amount DOUBLE NULL,
   PRIMARY KEY (Order_ID),
   INDEX ID (Customer_ID),
   CONSTRAINT
     FOREIGN KEY (Customer_ID)
     REFERENCES customer (ID)
     ON DELETE RESTRICT
     ON UPDATE RESTRICT
) ENGINE = InnoDB;




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

Most excellent post Jerry... Thank you very much..
You too luuk...
I was curious about the insert statement and it's syntax...
is there a notion of an insert crossing tables?  or must each insert
be restricted to one table?

On Thu, 27 Jan 2011 12:01:05 -0800 (PST), SpreadTooThin wrote:
> Most excellent post Jerry... Thank you very much..
> You too luuk...
> I was curious about the insert statement and it's syntax...
> is there a notion of an insert crossing tables?  or must each insert
> be restricted to one table?

Some RDBMS allow multi-table inserts. MySQL currently doesn't support
it.

Reading up on the LAST_INSERT_ID() function and/or the corresponding
functions in your host language API will make exactly how to manage the
auto-increment stuff that Jerry talked about very clear and easy.

-- 
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.                                  [TOFU := text oben,
Q: What is the most annoying thing on usenet?        followup unten]

On 1/27/2011 3:01 PM, SpreadTooThin wrote:
> Most excellent post Jerry... Thank you very much..
> You too luuk...
> I was curious about the insert statement and it's syntax...
> is there a notion of an insert crossing tables?  or must each insert
> be restricted to one table?
>

You will have two INSERT statements - one for the customer and one for 
the order.

But that's how it should be anyway.  While the tables have a 
relationship, you may insert into one or the other - not necessarily both.

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

Jerry Stuckle:

>On 1/27/2011 3:01 PM, SpreadTooThin wrote:

>>is there a notion of an insert crossing tables?  or must each insert
>>be restricted to one table?
>>
>
>You will have two INSERT statements - one for the customer and one
>for the order.

[This is meant as some advice/thoughts for OP, but written as a
follow-up to Jerry's post, because it is related to his correct
comments:]

There's something else to consider in this type of situations, though:
"what makes a person a Customer?"

Can a customer really have no Orders? If so, why is he considered a
Customer? Why not his neighbor and why not someone else? You don't keep
track of everyone on this planet, right?

There may be a good reason to only keep data of people, who have placed
at least one Order. Privacy reasons, legal reasons, or perhaps plain
application reasons (you don't want to keep, maintain and backup data
unnecessarily).
In that case, you need to set up your application in such a way, that
you gather Customer and Order data first (at least one Order) and then
store them in your database in _one_ transaction. That way, you make
sure, that Customers always have at least one Order. Should the Order
be deleted, then the Customer needs to be deleted automatically in the
same transaction.
If you allow data entry for the Customer first, and then initiate a
next step for Order entry, the operator might simply not enter the
Order and you are left with a Customer without Orders.
Some organizations have a nightly batch run that deletes such orphans,
but they would have Customers-without-Orders at least temporarily,
which is not desirable.

However, if a Customer is defined as a person who doesn't need to have
at least one order (he merely inquired about an article, or he placed
an order which got deleted later) you don't need transactions. It
wouldn't be a disaster if someone entered Customer data first, then
quit his job and never entered the Order that should be entered in the
next step. The data would still be valid.

I am not pleading in favor of either definition, but I do suggest you
consider both and make the proper choice for your situation.

=====

Oh, and another point, which _always_ comes up when designing such
application and the processes of the organization:
- how are you going to keep the Customer data up to date? Do you get
notified if a Customer moves to another address, or if he dies? If not,
won't you have useless data after several years?
One way to solve this, is sending all Customers a mail with their
stored data, asking them to confirm or correct them.
- how are you going to make sure that Customers aren't entered multiple
times? Is Peter Mueller, on Cross Road 34 in Kingston the same person
as Mr.P.M�ller at the same address? Your application needs some type of
logic that attempts to support the operator in identifying duplicates
upon data entry, and/or deleting duplicates afterwards.


-- 
Erick

El 27/01/2011 21:01, SpreadTooThin escribi�/wrote:
> I was curious about the insert statement and it's syntax...

As everything else, the syntax for INSERT is documented:

http://dev.mysql.com/doc/refman/5.1/en/insert.html

> is there a notion of an insert crossing tables?  or must each insert
> be restricted to one table?

As you can see in the above link, only one table is mentioned.

Whatever, other DBMS have this feature and the query can get complex to 
get right. Before caring about this kind of stuff you should get 
familiar with basic SQL syntax and also get used to the reference manual:

http://dev.mysql.com/doc/refman/5.1/en/



-- 
-- 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
--

> [This is meant as some advice/thoughts for OP, but written as a
> follow-up to Jerry's post, because it is related to his correct
> comments:]
> 
> There's something else to consider in this type of situations, though:
> "what makes a person a Customer?"
> 
> Can a customer really have no Orders? If so, why is he considered a
> Customer? Why not his neighbor and why not someone else? You don't keep
> track of everyone on this planet, right?

A person using your web site might be a Customer because he set up
an account with a username, password, and perhaps contact information
like email address, thereby expressing interest in buying something
(eventually, but no promises).  That way he can get a shopping cart
that persists between sessions, a wish list that follows him, and
you can tie his searches to his account.  He can also express
interest (or not) in receiving promotional material you send out
to interested customers.  The same applies to customers who sent you
postal mail asking for your catalog (perhaps even paying for it).

Don't put your customers in the awkward position of having to buy
something before you'll let him into the store to see the merchandise.
(or the electronic equivalent of the same thing).  They'll go
elsewhere.

Don't put your customer in the awkward position of not allowing him
to cancel his first order because the correct size is in question,
until he orders something else, not permitting him to take time to
find out the correct size (since this is a gift, he can't just phone
and ask directly).  He'll just go elsewhere.

> There may be a good reason to only keep data of people, who have placed
> at least one Order. Privacy reasons, legal reasons, or perhaps plain
> application reasons (you don't want to keep, maintain and backup data
> unnecessarily).

The trend seems to be in the opposite direction:  capture every
click the customer makes at your site, and try your darndest to
capture every bit of information about where else he's been surfing
the web also.

A compromise here might be to clean out customer records of those
who have never ordered that are older than some specified time (say,
6 months).  Or, if you're selling Super Bowl tickets, delete customer
records of those who didn't buy this year the day after the Super
Bowl, since it's unlikely they will buy Super Bowl tickets in the
next 6 months.

> In that case, you need to set up your application in such a way, that
> you gather Customer and Order data first (at least one Order) and then
> store them in your database in _one_ transaction. That way, you make
> sure, that Customers always have at least one Order. Should the Order
> be deleted, then the Customer needs to be deleted automatically in the
> same transaction.

Be really careful that this doesn't conflict with customer demands.
If an order gets fouled up for some reason (my fault or not), I'd
want it cancelled (and verification that it really did cancel, did
not ship, and I won't be billed) before starting another order.
Why?  To avoid double billing and/or double shipments.  If that
means I have to repeat all the customer info to start another order,
I'm not going to think your company knows how to do business very
well.

Depending on what you're using this database for, it is quite
possible you will *NEVER* allow customers to be deleted (although
they might be marked inactive), especially if they have ever had
any orders that involved money (even if it was a charge immediately
followed by a refund).  Customer orders from 2 years ago are part
of your financial data and the IRS expects you to have that data
in case of an audit, so you can justify totals put on your tax
return.  You may also have to be prepared to list all the orders
where you shipped certain chemicals (and where to) in the past 5
years in response to a subpoena from Homeland Security.  That
includes orders shipped to now-deleted customers.  That might even
include orders shipped and later cancelled.

It is also quite possible that you will *NEVER* allow an order to
be deleted, although they might be marked with a status like
"completed", "cancelled" or "cancellation-in-progress".  Certainly,
deleting an order can require extra processing before the deletion
should be allowed:  If the order is shipped but not paid for, get
the merchandise back or bill the customer.  If the order is not
shipped and paid for, give the customer a refund (which might involve
sending a check) unless it involves non-cancellable customization
like company logos on T-shirts.  If the customer is returning the
merchandise, you might need to bill him for a restocking fee.  If
you spent money shipping the order, you may have to keep the order
record around indefinitely to account for your balance due to the
shipping company and to account for the packing materials used.

> If you allow data entry for the Customer first, and then initiate a
> next step for Order entry, the operator might simply not enter the
> Order and you are left with a Customer without Orders.
> Some organizations have a nightly batch run that deletes such orphans,
> but they would have Customers-without-Orders at least temporarily,
> which is not desirable.

> However, if a Customer is defined as a person who doesn't need to have
> at least one order (he merely inquired about an article, or he placed
> an order which got deleted later) you don't need transactions. It

You may need a transaction to enter an order and a bunch (1 or more
- it's hard to imagine an order with 0 line-items) of line-items
for that order, especially if your database is tracking stock levels
and is supposed to flag line-items of an order that cannot be filled
with available stock.

> wouldn't be a disaster if someone entered Customer data first, then
> quit his job and never entered the Order that should be entered in the
> next step. The data would still be valid.

Often a customer himself will create that record, on a web site.


One thing you should also consider:  why cannot a single person be
nine Customers?  He might be representing:
	- himself
	- himself on behalf of his company expense account
	- minor family members who don't have credit cards (and who, for various reasons,
	  want separate accounts)
	- his church
	- several departments at his place of work
and want all the transactions grouped with the appropriate account,
since they should be independent of each other.

Also, why cannot a customer be multiple persons?  A particular
company may be represented by one of several employees at the company
authorized to buy for it, and the company wants them all on one
account.  Husbands and wives may both contact you about the same
account, and may want to share it.

If you sell much to non-individual-people, distinguish between the
organization and the contact.  Also don't make silly restrictions
like if two accounts have the same contact email address, they are
the same customer.  (Or worse, that if they have the same name like
John Smith, they are the same person and therefore the same customer).
Husbands and wives sometimes have a single email address (by choice,
not because it costs extra).  They also buy each other gifts and
don't want the surprise ruined by it showing up on the other's
account.

One wedding planner might be the contact address for 10% of your
customers (although in this case, you should capture which couple
this is for, as well as the contact info.)

> I am not pleading in favor of either definition, but I do suggest you
> consider both and make the proper choice for your situation.
> 
> =====
> 
> Oh, and another point, which _always_ comes up when designing such
> application and the processes of the organization:
> - how are you going to keep the Customer data up to date? Do you get
> notified if a Customer moves to another address, or if he dies? If not,
> won't you have useless data after several years?

Past financial data is not useless (think:  tax audit).  It may be
necessary to keep *PAST* addresses (perhaps copied to orders: this
is *not* redundant information) even if (and *because*) the address
has changed to be prepared for a sales tax audit of prior-year
orders.

For some companies, it is appropriate to change the person on the
address rather than the address of the person.  (Consider pest
control companies, roofing companies, real estate agents, lawn
maintenance companies, etc.  Most of the information goes with the
property, not the person.)

Past marketing data may become useless.  It might be worthwhile to
keep track of things like last purchase, last visit to the web site,
etc. to see if it's dead.   Also if you send out advertising material
with "address correction requested", you might get notified by the
post office.  Also permitting the customer to edit his own profile
may prove useful.  Assigning and using account numbers in mail/email
is useful, if for no other reason that you can identify which account
is associated with bouncing mail/email.

> One way to solve this, is sending all Customers a mail with their
> stored data, asking them to confirm or correct them.

Letting them change their profile on the web site, and at appropriate
times (like at the time of an order) show them the default shipping
address, and ask them whether to use it and to correct it if necessary
is likely to work better for active customers.  

If a company asks me to update a bunch of personal info by email
or postal mail on a periodic basis, especially if that is the entire
content of the message, the first thing I think of is "phishing",
and the next thing I think of is that they lost my info.  Neither
makes me want to do business with that company again.  It looks a
lot less incompetent if this is combined with a statement or bill
which the customer will need to respond to anyway.  Leave space on
the return slip that goes with a payment for address / phone number
/ name changes.  Most companies that send bills to me do this.

> - how are you going to make sure that Customers aren't entered multiple
> times? Is Peter Mueller, on Cross Road 34 in Kingston the same person
> as Mr.P.M?ller at the same address? Your application needs some type of
> logic that attempts to support the operator in identifying duplicates
> upon data entry, and/or deleting duplicates afterwards.

Is it really important?  If I always use my *NEW* ebay account, and
I never use the one I may or may not have created 15 years ago,
does it really matter that it still exists?  The fact that it's
been unused for at least 13 years should be a big hint it's inactive.
On the other hand, if a person gets randomly associated with one
of his multiple accounts for each order, billing will be a mess.

Duplicate accounts tend to vanish if you bill for them.  This is
one reason why a credit card annual fee of one cent per year can
do a lot to clean out old records, although banks generally get
much greedier than that.  That can put off customers in other
situations, though.