List:General Discussion« Previous MessageNext Message »
From:metastable Date:November 17 2008 5:55pm
Subject:Re: normalised designs: customer database
View as plain text  
Jerry Schwartz wrote:
> If all you want to do is to restrict a field to certain values, and aren't
> concerned with cascading operations, is a set more efficient than a foreign
> key?
>
> Regards,
>  
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>  
> 860.674.8796 / FAX: 860.674.8341
>  
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>
>
>
>
>
>   
Hey,

If efficiency were an issue for 90% of the applications out there, we'd
all be in big trouble. This isn't kernels or apache servers we're
discussing.
What is important, especially to a small contractor such as myself, is
ease of design, implementation and debugging.

I've used the enum approach in the past. Here's what the code for a
typical query build looks like:
---------
$query .= 'CASE ';
$query .= 'WHEN ' . getTable('content') . ".`size` = '%s' THEN (" .
getTable('content_strings') . '.`language`) ';
$query .= 'WHEN ' . getTable('content') . ".`size` = '%s' THEN (" .
getTable('content_text') . '.`language`) ';
$query .= "ELSE '' ";
$query .= 'END AS `label_language`, ';
$args[] = ContentItem::SIZE_STRING;
$args[] = ContentItem::SIZE_TEXT;
----------
(This is for any text item that can be translated)
And repeat for each and every 'multiplexed' item you require.

Apart from the incredibly complex queries this produces, this also
ensures many hours spent debugging.
In the solution I came up with, I wouldn't have to go through this.
I query from the customers table what I need, and only when the time
comes that I'm printing the actual invoice (for instance), I make
further queries to the differentiated tables for customer types.
All data neatly wrapped into it's own design structure, as the lord god
intended.

You may say that the above query is overly complex and futile, and that
different approaches are better. I would agree with you, were it not for
the fact that I like to program with a 'framework mindset': I want my
code to be reusable and not too complex for possible future collaborators.



Best regards,


Stijn





Thread
normalised designs: customer databasemetastable14 Nov
  • Re: normalised designs: customer databaseMark Goodge14 Nov
    • Re: normalised designs: customer databaseMr. Shawn H. Corey14 Nov
      • Re: normalised designs: customer databasemetastable14 Nov
  • RE: normalised designs: customer databaseJerry Schwartz14 Nov
  • Re: normalised designs: customer databaseJujitsu Lizard14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
    • RE: normalised designs: customer databaseUS Data Export14 Nov
      • Re: normalised designs: customer databasemetastable14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
    • Re: normalised designs: customer databaseJujitsu Lizard14 Nov
      • Re: normalised designs: customer databasePeter Brawley14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies14 Nov
    • Re: normalised designs: customer databaseJujitsu Lizard14 Nov
      • Re: normalised designs: customer databasemetastable14 Nov
    • Re: normalised designs: customer databasemetastable15 Nov
      • Re: normalised designs: customer databaseJujitsu Lizard15 Nov
        • Re: normalised designs: customer databasemetastable16 Nov
          • RE: normalised designs: customer databaseJerry Schwartz17 Nov
            • Re: normalised designs: customer databasemetastable17 Nov
  • Re: normalised designs: customer databaseBill newton14 Nov
  • Re: normalised designs: customer databaseMartijn Tonies17 Nov
  • Re: normalised designs: customer databaseMartijn Tonies17 Nov
  • Re: normalised designs: customer databaseMartijn Tonies17 Nov
    • RE: normalised designs: customer databaseUS Data Export17 Nov
  • Re: normalised designs: customer databaseMartijn Tonies17 Nov