Check record reference by other tables

On 10 Aug, 13:44, Jerry Stuckle wrote:
> Captain Paralytic wrote:
> > On 10 Aug, 10:32, cell wrote:
> >> Hello all,
>
> >> Suppose I have 2 tables order and product.
>
> >> In order table it have a field named product_id which refers to
> >> primary key ID of product table.
> >> e load
> >> Now someone wants to change details in product table such as the price
> >> but this action will affect the old data in order table as it takes
> >> product information such as name, part number and price from product
> >> table.
>
> >> So I want to ask whether there exists commands in Mysql that can alert
> >> me when the product ID is referenced by other table.
>
> >> One method is split the relation, just copy the price, part number and
> >> description to the order table, but this weaken the benefit of
> >> relational database and burden the table size.
>
> >> Another method is to check whether the product ID is referenced
> >> manually by SQL before modifying anything in product table. However,
> >> it there any method more robust as the product table might be
> >> referenced by many other tables?
>
> >> Thanks!
> >> Terry.
>
> > Hi Terry,
> > depending on which point of view (PoV) one takes, you have either over
> > normalised or under designed your schema.
>
> > The particular scenario you have is an "uncomfortable" one from the
> > PoV of normalisation.
>
> > There are (at least) 2 ways of approaching this, depending on the
> > amount of flexibility required.
>
> > When reading this, bear in mind that all design is a compromise. We
> > swap source file size and possible execution efficiency for comments
> > and readability.
>
> > Frequently in data warehousing applications, which would benefit from
> > the size benefits of normalisation, the data in in fact de-normalised
> > to improve access speed via indexes.
>
> > The method that I suspect is more common, is where items in orders are
> > considered different entities from those in stock. So once an item is
> > purchased, the de-normalised data is written to the order table. This
> > is then a permanent record of the scenario at the time of ordering. It
> > also allows one to do a special "one off" adjustment of the price
> > (maybe it is a "shop-soiled" item).
>
> > Of course, this method does not cater for a really "honest" trading
> > approach, where say, you have 3 old stock items and new stock arrives
> > at a higher price. If someone wants 5 items, the honest approach is to
> > charge the old price for 3 items and the new price for 2.
>
> > In order to do this, you need the approach that Lennart referred to,
> > where all items have effective dates (plus in this case a method to
> > handle usage of remaining stock from a previous date).
>
> > The problem with the fully normalised approach, is that it is often
> > very difficult to get clients, who have that dangerous "little
> > knowledge", to understand that they must never delete a record from a
> > reference (e.g. a user/customer) table, but should only mark he
> > records as obsolete. I have just recently had someone delete all the
> > users who no longer had involvement with their company and "lo and
> > behold", many of the queries with JOINs to that table are no longer
> > showing all the results that they did before.
>
> I'll disagree with your comment on "honest trading", Paul.

It's OK, because I disagree with it too, hence the quotation marks.
However, in the old days of supermarkets (before shelf edge labels,
when items were individually priced with pricing guns) and certainly
still the case in some small shops, you do find some items with the
"old" price and then some with the "new" price on them. It's certainly
the case in the Asian food shop that I use.
 >> Stay informed about: Check record reference by other tables 

On 10 Aug, 13:41, Jerry Stuckle wrote:
> In addition to what Paul said, keeping the item price in the order
> allows for things like "one day only" specials, where the price is
> changed for a single day, then changed back again.

Well I did "sort of" cover this when I said:

"It also allows one to do a special "one off" adjustment of the
price" (only you have to expand the meaning to more than one instance
of the item).
 >> Stay informed about: Check record reference by other tables 

On 2010-08-10 11:32, cell wrote:
> Hello all,
>
> Suppose I have 2 tables order and product.
>
> In order table it have a field named product_id which refers to
> primary key ID of product table.
> e load
> Now someone wants to change details in product table such as the price
> but this action will affect the old data in order table as it takes
> product information such as name, part number and price from product
> table.
>
> So I want to ask whether there exists commands in Mysql that can alert
> me when the product ID is referenced by other table.
>
> One method is split the relation, just copy the price, part number and
> description to the order table, but this weaken the benefit of
> relational database and burden the table size.
>
> Another method is to check whether the product ID is referenced
> manually by SQL before modifying anything in product table. However,
> it there any method more robust as the product table might be
> referenced by many other tables?
>

What you possibly miss is a relation describing the prize of a product
at a certain point in time. You can download:

http://www.cs.arizona.edu/~rts/tdbbook.pdf

for free. It might give you some ideas.


/Lennart
 >> Stay informed about: Check record reference by other tables 

cell wrote:
> Hello all,
>
> Suppose I have 2 tables order and product.
>
> In order table it have a field named product_id which refers to
> primary key ID of product table.
> e load
> Now someone wants to change details in product table such as the price
> but this action will affect the old data in order table as it takes
> product information such as name, part number and price from product
> table.
>
> So I want to ask whether there exists commands in Mysql that can alert
> me when the product ID is referenced by other table.
>
> One method is split the relation, just copy the price, part number and
> description to the order table, but this weaken the benefit of
> relational database and burden the table size.
>
> Another method is to check whether the product ID is referenced
> manually by SQL before modifying anything in product table. However,
> it there any method more robust as the product table might be
> referenced by many other tables?
>
> Thanks!
> Terry.

In addition to what Paul said, keeping the item price in the order
allows for things like "one day only" specials, where the price is
changed for a single day, then changed back again.

One other fairly normalized way I've seen is to keep track of product
prices by dates, i.e.

price_id product_id start_date end_date price
1 12345 2010-06-01 2010-07-31 4.95
2 12345 2010-08-01 NULL 5.15 -- no end date
3 34567 2010-01-01 NULL 9.95

But this rapidly can get awkward and hard to manage. And although it is
more normalized than individual prices in the order table, it's seldom
used because of those problems.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Check record reference by other tables 

Captain Paralytic wrote:
> On 10 Aug, 10:32, cell wrote:
>> Hello all,
>>
>> Suppose I have 2 tables order and product.
>>
>> In order table it have a field named product_id which refers to
>> primary key ID of product table.
>> e load
>> Now someone wants to change details in product table such as the price
>> but this action will affect the old data in order table as it takes
>> product information such as name, part number and price from product
>> table.
>>
>> So I want to ask whether there exists commands in Mysql that can alert
>> me when the product ID is referenced by other table.
>>
>> One method is split the relation, just copy the price, part number and
>> description to the order table, but this weaken the benefit of
>> relational database and burden the table size.
>>
>> Another method is to check whether the product ID is referenced
>> manually by SQL before modifying anything in product table. However,
>> it there any method more robust as the product table might be
>> referenced by many other tables?
>>
>> Thanks!
>> Terry.
>
> Hi Terry,
> depending on which point of view (PoV) one takes, you have either over
> normalised or under designed your schema.
>
> The particular scenario you have is an "uncomfortable" one from the
> PoV of normalisation.
>
> There are (at least) 2 ways of approaching this, depending on the
> amount of flexibility required.
>
> When reading this, bear in mind that all design is a compromise. We
> swap source file size and possible execution efficiency for comments
> and readability.
>
> Frequently in data warehousing applications, which would benefit from
> the size benefits of normalisation, the data in in fact de-normalised
> to improve access speed via indexes.
>
> The method that I suspect is more common, is where items in orders are
> considered different entities from those in stock. So once an item is
> purchased, the de-normalised data is written to the order table. This
> is then a permanent record of the scenario at the time of ordering. It
> also allows one to do a special "one off" adjustment of the price
> (maybe it is a "shop-soiled" item).
>
> Of course, this method does not cater for a really "honest" trading
> approach, where say, you have 3 old stock items and new stock arrives
> at a higher price. If someone wants 5 items, the honest approach is to
> charge the old price for 3 items and the new price for 2.
>
> In order to do this, you need the approach that Lennart referred to,
> where all items have effective dates (plus in this case a method to
> handle usage of remaining stock from a previous date).
>
> The problem with the fully normalised approach, is that it is often
> very difficult to get clients, who have that dangerous "little
> knowledge", to understand that they must never delete a record from a
> reference (e.g. a user/customer) table, but should only mark he
> records as obsolete. I have just recently had someone delete all the
> users who no longer had involvement with their company and "lo and
> behold", many of the queries with JOINs to that table are no longer
> showing all the results that they did before.


I'll disagree with your comment on "honest trading", Paul. This assumes
the selling price is determined by the purchase price, i.e. a fixed
markup. But that is not the case; purchase price of course is involved
in setting the selling price (i.e. you don't want to sell an item which
costs you $1,000 for $1). But that's not the only consideration
involved. Additionally, it's entirely possible that if the cost goes
up, the selling price may not.

I have another business where I get stuff from several distributors.
Prices often differ a small amount; where I get them from is not always
the cheapest for many reasons, and prices change regularly, i.e. monthly
sales, cost increases to them, etc. But all the items are the same
(manufacturer, part number, etc.), and my price on the items remain the
same, no matter where I get them.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex RemoveThis @attglobal.net
==================
 >> Stay informed about: Check record reference by other tables 

Captain Paralytic wrote:
> On 10 Aug, 13:44, Jerry Stuckle wrote:
>> Captain Paralytic wrote:
>>> On 10 Aug, 10:32, cell wrote:
>>>> Hello all,
>>>> Suppose I have 2 tables order and product.
>>>> In order table it have a field named product_id which refers to
>>>> primary key ID of product table.
>>>> e load
>>>> Now someone wants to change details in product table such as the price
>>>> but this action will affect the old data in order table as it takes
>>>> product information such as name, part number and price from product
>>>> table.
>>>> So I want to ask whether there exists commands in Mysql that can alert
>>>> me when the product ID is referenced by other table.
>>>> One method is split the relation, just copy the price, part number and
>>>> description to the order table, but this weaken the benefit of
>>>> relational database and burden the table size.
>>>> Another method is to check whether the product ID is referenced
>>>> manually by SQL before modifying anything in product table. However,
>>>> it there any method more robust as the product table might be
>>>> referenced by many other tables?
>>>> Thanks!
>>>> Terry.
>>> Hi Terry,
>>> depending on which point of view (PoV) one takes, you have either over
>>> normalised or under designed your schema.
>>> The particular scenario you have is an "uncomfortable" one from the
>>> PoV of normalisation.
>>> There are (at least) 2 ways of approaching this, depending on the
>>> amount of flexibility required.
>>> When reading this, bear in mind that all design is a compromise. We
>>> swap source file size and possible execution efficiency for comments
>>> and readability.
>>> Frequently in data warehousing applications, which would benefit from
>>> the size benefits of normalisation, the data in in fact de-normalised
>>> to improve access speed via indexes.
>>> The method that I suspect is more common, is where items in orders are
>>> considered different entities from those in stock. So once an item is
>>> purchased, the de-normalised data is written to the order table. This
>>> is then a permanent record of the scenario at the time of ordering. It
>>> also allows one to do a special "one off" adjustment of the price
>>> (maybe it is a "shop-soiled" item).
>>> Of course, this method does not cater for a really "honest" trading
>>> approach, where say, you have 3 old stock items and new stock arrives
>>> at a higher price. If someone wants 5 items, the honest approach is to
>>> charge the old price for 3 items and the new price for 2.
>>> In order to do this, you need the approach that Lennart referred to,
>>> where all items have effective dates (plus in this case a method to
>>> handle usage of remaining stock from a previous date).
>>> The problem with the fully normalised approach, is that it is often
>>> very difficult to get clients, who have that dangerous "little
>>> knowledge", to understand that they must never delete a record from a
>>> reference (e.g. a user/customer) table, but should only mark he
>>> records as obsolete. I have just recently had someone delete all the
>>> users who no longer had involvement with their company and "lo and
>>> behold", many of the queries with JOINs to that table are no longer
>>> showing all the results that they did before.
>> I'll disagree with your comment on "honest trading", Paul.
>
> It's OK, because I disagree with it too, hence the quotation marks.
> However, in the old days of supermarkets (before shelf edge labels,
> when items were individually priced with pricing guns) and certainly
> still the case in some small shops, you do find some items with the
> "old" price and then some with the "new" price on them. It's certainly
> the case in the Asian food shop that I use.

Yep, I remember those days, also - looking through the items to see if
you can save $0.05.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.RemoveThis@attglobal.net
==================
 >> Stay informed about: Check record reference by other tables 

Captain Paralytic wrote:
> On 10 Aug, 13:41, Jerry Stuckle wrote:
>> In addition to what Paul said, keeping the item price in the order
>> allows for things like "one day only" specials, where the price is
>> changed for a single day, then changed back again.
>
> Well I did "sort of" cover this when I said:
>
> "It also allows one to do a special "one off" adjustment of the
> price" (only you have to expand the meaning to more than one instance
> of the item).

Ah, OK, I didn't realize that's what you meant. Sorry!

The joys of separation by a common language

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.RemoveThis@attglobal.net
==================
 >> Stay informed about: Check record reference by other tables 

On Aug 10, 8:02 pm, Captain Paralytic wrote:
> On 10 Aug, 10:32, cell wrote:
>
>
>
> > Hello all,
>
> > Suppose I have 2 tables order and product.
>
> > In order table it have a field named product_id which refers to
> > primary key ID of product table.
> > e load
> > Now someone wants to change details in product table such as the price
> > but this action will affect the old data in order table as it takes
> > product information such as name, part number and price from product
> > table.
>
> > So I want to ask whether there exists commands in Mysql that can alert
> > me when the product ID is referenced by other table.
>
> > One method is split the relation, just copy the price, part number and
> > description to the order table, but this weaken the benefit of
> > relational database and burden the table size.
>
> > Another method is to check whether the product ID is referenced
> > manually by SQL before modifying anything in product table. However,
> > it there any method more robust as the product table might be
> > referenced by many other tables?
>
> > Thanks!
> > Terry.
>
> Hi Terry,
> depending on which point of view (PoV) one takes, you have either over
> normalised or under designed your schema.
>
> The particular scenario you have is an "uncomfortable" one from the
> PoV of normalisation.
>
> There are (at least) 2 ways of approaching this, depending on the
> amount of flexibility required.
>
> When reading this, bear in mind that all design is a compromise. We
> swap source file size and possible execution efficiency for comments
> and readability.
>
> Frequently in data warehousing applications, which would benefit from
> the size benefits of normalisation, the data in in fact de-normalised
> to improve access speed via indexes.
>
> The method that I suspect is more common, is where items in orders are
> considered different entities from those in stock. So once an item is
> purchased, the de-normalised data is written to the order table. This
> is then a permanent record of the scenario at the time of ordering. It
> also allows one to do a special "one off" adjustment of the price
> (maybe it is a "shop-soiled" item).
>
> Of course, this method does not cater for a really "honest" trading
> approach, where say, you have 3 old stock items and new stock arrives
> at a higher price. If someone wants 5 items, the honest approach is to
> charge the old price for 3 items and the new price for 2.
>
> In order to do this, you need the approach that Lennart referred to,
> where all items have effective dates (plus in this case a method to
> handle usage of remaining stock from a previous date).
>
> The problem with the fully normalised approach, is that it is often
> very difficult to get clients, who have that dangerous "little
> knowledge", to understand that they must never delete a record from a
> reference (e.g. a user/customer) table, but should only mark he
> records as obsolete. I have just recently had someone delete all the
> users who no longer had involvement with their company and "lo and
> behold", many of the queries with JOINs to that table are no longer
> showing all the results that they did before.

Thank you very much for all advice posting here.

Today I think a possible approach but might not be a good one:

The product has referential integrity protection by foreign key in
order table and/or other tables.

Firstly I try to delete the product record, if it generates
referential integrity error, this means it is "used" by other
table(s).

So I need to copy the record and set the old record obselete.

Otherwise it means that there is no references from other table(s) and
user can change the price and other infomation "freely". But I need to
insert back the record in the table after that.

If there is no need to remove and regenerate record again, and there
exists a function which returns having or not references by other
table in SQL, it will become better or perfect.

Or this is just my silly lack of knowledge about MySQL as actually it
exists?

BR,
Terry.
 >> Stay informed about: Check record reference by other tables 

On 2010-08-11 06:14, cell wrote:
[...]
>
> Firstly I try to delete the product record, if it generates
> referential integrity error, this means it is "used" by other
> table(s).
>

Note that instead of trying to delete, you can investigate whether there
is a referring row or not. You can generate statements to investigate
this via:

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE

/Lennart

[...]
 >> Stay informed about: Check record reference by other tables 

cell wrote:
> On Aug 10, 8:02 pm, Captain Paralytic wrote:
>> On 10 Aug, 10:32, cell wrote:
>>
>>
>>
>>> Hello all,
>>> Suppose I have 2 tables order and product.
>>> In order table it have a field named product_id which refers to
>>> primary key ID of product table.
>>> e load
>>> Now someone wants to change details in product table such as the price
>>> but this action will affect the old data in order table as it takes
>>> product information such as name, part number and price from product
>>> table.
>>> So I want to ask whether there exists commands in Mysql that can alert
>>> me when the product ID is referenced by other table.
>>> One method is split the relation, just copy the price, part number and
>>> description to the order table, but this weaken the benefit of
>>> relational database and burden the table size.
>>> Another method is to check whether the product ID is referenced
>>> manually by SQL before modifying anything in product table. However,
>>> it there any method more robust as the product table might be
>>> referenced by many other tables?
>>> Thanks!
>>> Terry.
>> Hi Terry,
>> depending on which point of view (PoV) one takes, you have either over
>> normalised or under designed your schema.
>>
>> The particular scenario you have is an "uncomfortable" one from the
>> PoV of normalisation.
>>
>> There are (at least) 2 ways of approaching this, depending on the
>> amount of flexibility required.
>>
>> When reading this, bear in mind that all design is a compromise. We
>> swap source file size and possible execution efficiency for comments
>> and readability.
>>
>> Frequently in data warehousing applications, which would benefit from
>> the size benefits of normalisation, the data in in fact de-normalised
>> to improve access speed via indexes.
>>
>> The method that I suspect is more common, is where items in orders are
>> considered different entities from those in stock. So once an item is
>> purchased, the de-normalised data is written to the order table. This
>> is then a permanent record of the scenario at the time of ordering. It
>> also allows one to do a special "one off" adjustment of the price
>> (maybe it is a "shop-soiled" item).
>>
>> Of course, this method does not cater for a really "honest" trading
>> approach, where say, you have 3 old stock items and new stock arrives
>> at a higher price. If someone wants 5 items, the honest approach is to
>> charge the old price for 3 items and the new price for 2.
>>
>> In order to do this, you need the approach that Lennart referred to,
>> where all items have effective dates (plus in this case a method to
>> handle usage of remaining stock from a previous date).
>>
>> The problem with the fully normalised approach, is that it is often
>> very difficult to get clients, who have that dangerous "little
>> knowledge", to understand that they must never delete a record from a
>> reference (e.g. a user/customer) table, but should only mark he
>> records as obsolete. I have just recently had someone delete all the
>> users who no longer had involvement with their company and "lo and
>> behold", many of the queries with JOINs to that table are no longer
>> showing all the results that they did before.
>
> Thank you very much for all advice posting here.
>
> Today I think a possible approach but might not be a good one:
>
> The product has referential integrity protection by foreign key in
> order table and/or other tables.
>
> Firstly I try to delete the product record, if it generates
> referential integrity error, this means it is "used" by other
> table(s).
>
> So I need to copy the record and set the old record obselete.
>
> Otherwise it means that there is no references from other table(s) and
> user can change the price and other infomation "freely". But I need to
> insert back the record in the table after that.
>
> If there is no need to remove and regenerate record again, and there
> exists a function which returns having or not references by other
> table in SQL, it will become better or perfect.
>
> Or this is just my silly lack of knowledge about MySQL as actually it
> exists?
>
> BR,
> Terry.

Not a lack of knowledge - but a poor database design.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex RemoveThis @attglobal.net
==================
 >> Stay informed about: Check record reference by other tables 

>Of course, this method does not cater for a really "honest" trading
>approach,

For that read "dishonest and possibly illegal".

>where say, you have 3 old stock items and new stock arrives
>at a higher price. If someone wants 5 items, the honest approach is to
>charge the old price for 3 items and the new price for 2.

No, the honest approach is to charge no more than the lesser of
your advertised price or the price on your price tags, regardless
of how much it cost. Otherwise it's called bait-and-switch, and
possibly false advertising. Refusing to sell the item at all may
also be bait-and-switch, unless you're really sold out. That doesn't
rule out changing the price tags (up or down) before the customer
picks up the item but recalling your advertisements may not be so
easy. You can, of course, advertise a 1-day sale with special
prices and after that day, raise them, or advertise that prices are
good to the end of the week, and raise them in your next ad for the
next week.

>In order to do this, you need the approach that Lennart referred to,
>where all items have effective dates (plus in this case a method to
>handle usage of remaining stock from a previous date).

I don't believe a store can really nail down prices so tight that
a price-by-date setup can work. You really need to record the price
the item was actually sold at.

Reasons why the price might not stay put: quantity discounts (some
stores tape a limited number of two $4 not-necessarily-identical
items together and mark the result $7.50). Some stores really mean
it when they say: $1.20 or 10/$10, so if a customer buys 12, two
of them are more expensive than the others. Damaged or near-expired
items get marked down. Employee discounts. Customer haggling.
Simple error. There are some other ways of accounting for some of
this, like making a discount a line item on the invoice, but not
all of them. Different stores may be in different time zones, which
combined with extended hours may make for some confusion over when
a price change takes effect in each store. And when does the price
change take place, when the item is rung up or when the whole sale
is complete? When does the sale complete? When the cashier pushes
the "TOTAL" button, or when he releases it? When the customer
clicks the "CHECKOUT" button, or when he clicks the final order
confirmation (during which time the price might have changed from
the last total he saw.)

You can get yourself in a lot of trouble if a mistake is made and
prices are mistakenly changed on already-sold products (something
that seems easy to do with a price-by-date table setup, especially
if someone was a little late changing the price). Your customer
accounts and revenue received don't balance any more. The invoices
you sent to customers no longer match your records for the same
customer, which will drive your customer service reps nuts trying
to resolve unrelated problems with customer bills. Tax authorities
may be wondering why there is a discrepancy, and the amount of tax
collected doesn't match the correct tax on the merchandise total.
It also may be difficult to track down. If you really made a mistake
(say, changing the price on the wrong product) and charged a grossly
wrong price for a product, then fixed it, you may have trouble
determining who actually paid the wrong price.

>The problem with the fully normalised approach, is that it is often

The price at which something actually sold is *not* a redundant
piece of information in most businesses and omitting it is not
"normalization", it's a mistake.

The amount of sales tax billed on the transaction also isn't a
redundant piece of information.

>very difficult to get clients, who have that dangerous "little
>knowledge", to understand that they must never delete a record from a
>reference (e.g. a user/customer) table, but should only mark he
>records as obsolete.

The same applies to changing prices when you haven't recorded the
prices something actually sold at.

>I have just recently had someone delete all the
>users who no longer had involvement with their company and "lo and
>behold", many of the queries with JOINs to that table are no longer
>showing all the results that they did before.
 >> Stay informed about: Check record reference by other tables 

On 11 Aug, 21:22, gordonb.ee....DeleteThis@burditt.org (Gordon Burditt) wrote:
> >Of course, this method does not cater for a really "honest" trading
> >approach,
>
> For that read "dishonest and possibly illegal".
>
> >where say, you have 3 old stock items and new stock arrives
> >at a higher price. If someone wants 5 items, the honest approach is to
> >charge the old price for 3 items and the new price for 2.
>
> No, the honest approach is to charge no more than the lesser of

Loads of stuff snipped.

Gordon, you do seem to have an uncanny knack of picking up small
pieces of information given as examples of type and then going into
detailed (not always correct) diatribes about them, when they have
absolutely nothing to do with the central theme of the thread!
 >> Stay informed about: Check record reference by other tables