New Book Teardown #1: The Art of Electronics 3rd Edition (2015) | In The Lab With Jay Jay

Posted on by
Reply

This post is part of my video blog and you can find more information about this video on this show’s homepage which is here.

You can support this channel on Patreon: patreon.com/JohnElliotV

Silly Job Title: Charge Charmer

This video is part of the New Book feature of my video blog.

In this video I review the venerable The Art of Electronics 3rd Edition by Paul Horowitz by Winfield Hill published in 2015. This monstrous tome includes some 1,220 pages.

This is a long video, because this is a long book!

While I was writing up these notes for the video I found a wealth of fun stuff. Here are a few links:

Thanks very much for watching! And please remember to hit like and subscribe!

Following is a product I use picked at random from my collection which may appear in my videos. Clicking through on this to find and click on the green affiliate links before purchasing from eBay or AliExpress is a great way to support the channel at no cost to you. Thanks!

Plato Model 170 Wire CutterThis is an image of the product.notes

Let’s go shopping!

Dependency Injection in MediaWiki

Posted on by
4

Today I read the documentation for how Dependency Injection is done in MediaWiki.

I had a few small nits to pick (such as their statement that services should be stateless) but… fair enough.

For myself I don’t usually use Dependency Injection and a Service Container, instead I prefer the Service Locator pattern which I find is simpler and more economical to use, especially in PHP where there is a single process per request.

Extreme late binding

Posted on by
Reply

There’s a famous quote from Alan Kay that you will see bandied about:

OOP to me means only messaging, local retention and protection and hiding of state-process, and extreme late-binding of all things.

I think I agree with him, but I often find myself wondering quite what he meant by “extreme late-binding”. I suspect he means that object instances can redefine stuff they inherited from their concrete class. If your OOP platform doesn’t support that, you can emulate it with data and API.

On the value of foreign key constraints

Posted on by
Reply

Here is something I would like to demo regarding foreign key constraints.

This regards what type of errors foreign key constraints can (and can’t!) save you from.

Occasionally you hear developers say how important foreign key constraints are and how foreign key constraints saved them from some bug. That might all seem well and good but when you think it through if a foreign key constraint saved you from a bug it also might very well just have been luck that saved you and the thing that went wrong might have gone wrong another way and have gone undetected as database corruption and potential accidental data disclosure to an incorrect entity.

To set the scene let’s use some schema/data from what we were talking about earlier:

create table t_skinny_customer (
  customer_id bigint not null primary key,
  customer_name varchar(255) not null,
  customer_record_created_on timestamp default current_timestamp,
  customer_record_updated_on timestamp default current_timestamp on update current_timestamp
);

create table t_skinny_customer_phone (
  customer_phone_id bigint not null primary key,
  customer_id bigint not null references t_skinny_customer( customer_id ) on delete cascade,
  phone_number varchar(255) not null,
  phone_type enum( 'phone', 'tollfree', 'mobile' ) not null default 'phone',
  phone_type_order tinyint unsigned not null default 0,
  phone_record_created_on timestamp default current_timestamp,
  phone_record_updated_on timestamp default current_timestamp on update current_timestamp
);

insert into t_skinny_customer (
  customer_id,
  customer_name
)
values (
  1,
  'John Doe'
), (
  2,
  'Jane Doe'
);

insert into t_skinny_customer_phone (
  customer_phone_id,
  customer_id,
  phone_number,
  phone_type,
  phone_type_order
)
values (
  11,
  1,
  '123-456-7890',
  'phone',
  1
), (
  21,
  2,
  '123-456-7894',
  'phone',
  1
);

Then let’s run this code:

    $stmt = $pdo->prepare("
      update
        t_skinny_customer_phone
      set
        customer_id = :customer_id
      where
        customer_phone_id = :phone_id
    ");

    try {

      $stmt->execute([
        'customer_id' => 3,
        'phone_id' => 11,
      ]);

      assert( false );

    }
    catch ( PDOException $ex ) {

      // 2024-02-13 jj5 - foreign key constraint saved the day!

      assert(
        0 === strpos(
          $ex->getMessage(),
          'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails'
        )
      );

    }

    $stmt->execute([
      'customer_id' => 2,
      'phone_id' => 11,
    ]);

    // 2024-02-13 jj5 - ... whoops! what we've done here is switch
    // one of John Doe's phone numbers to be one of Jane Doe's
    // phone numbers. The foreign key constraint is of no value in
    // detecting this type of data corruption.

Now I’m not saying that you shouldn’t have foreign key constraints. I’m just pointing out that if you do have foreign key constraints and you’re relying on them for “referential integrity” your door may be open for all manner of bugs (and exploits).

That your foreign key points to an actual record doesn’t necessarily imply that your foreign key points to a correct record.

Database design and normalization

Posted on by
7

I would like to compare and contrast two options for the design of customer contact information for a relational database.

This is the first option, a “fat” customer table.

create table t_fat_customer (
  customer_id bigint not null primary key,
  customer_name varchar(255) not null,
  customer_phone_1 varchar(255) not null default '',
  customer_phone_2 varchar(255) not null default '',
  customer_tollfree_1 varchar(255) not null default '',
  customer_tollfree_2 varchar(255) not null default '',
  customer_mobile_1 varchar(255) not null default '',
  customer_mobile_2 varchar(255) not null default '',
  customer_record_created_on timestamp default current_timestamp,
  customer_record_updated_on timestamp default current_timestamp on update current_timestamp
);

insert into t_fat_customer (
  customer_id,
  customer_name,
  customer_phone_1,
  customer_phone_2,
  customer_tollfree_1,
  customer_tollfree_2,
  customer_mobile_1,
  customer_mobile_2
)
values (
  1,
  'John Doe',
  '123-456-7890',
  '123-456-7891',
  '800-123-4567',
  '',
  '123-456-7892',
  ''
), (
  2,
  'Jane Doe',
  '123-456-7894',
  '123-456-7895',
  '800-123-4569',
  '',
  '123-456-7896',
  ''
);

This is the second option, a “skinny” and “properly normalized” option. It has lots of skinny tables, which some will claim are the hallmarks of “good design”.

create table t_skinny_customer (
  customer_id bigint not null primary key,
  customer_name varchar(255) not null,
  customer_record_created_on timestamp default current_timestamp,
  customer_record_updated_on timestamp default current_timestamp on update current_timestamp
);

create table t_skinny_customer_phone (
  customer_phone_id bigint not null primary key,
  customer_id bigint not null references t_skinny_customer( customer_id ) on delete cascade,
  phone_number varchar(255) not null,
  phone_type enum( 'phone', 'tollfree', 'mobile' ) not null default 'phone',
  phone_type_order tinyint unsigned not null default 0,
  phone_record_created_on timestamp default current_timestamp,
  phone_record_updated_on timestamp default current_timestamp on update current_timestamp
);

insert into t_skinny_customer (
  customer_id,
  customer_name
)
values (
  1,
  'John Doe'
), (
  2,
  'Jane Doe'
);

insert into t_skinny_customer_phone (
  customer_phone_id,
  customer_id,
  phone_number,
  phone_type,
  phone_type_order
)
values (
  11,
  1,
  '123-456-7890',
  'phone',
  1
), (
  12,
  1,
  '123-456-7891',
  'phone',
  2
), (
  13,
  1,
  '800-123-4567',
  'tollfree',
  1
), (
  14,
  1,
  '123-456-7892',
  'mobile',
  1
), (
  21,
  2,
  '123-456-7894',
  'phone',
  1
), (
  22,
  2,
  '123-456-7895',
  'phone',
  2
), (
  23,
  2,
  '800-123-4569',
  'tollfree',
  1
), (
  24,
  2,
  '123-456-7896',
  'mobile',
  1
);

Here are some stats from the design options:

Fat option Skinny option
Lines of code: 42 85
Tables: 1 2
Columns: 10 11
Records: 2 10

The first option is simpler than the second option by every measure.

I know, you want to create a more general solution. I know, they say a hallmark of good database design and “proper normalization” is lots of skinny tables. I remind you: the first option is simpler than the second option by every measure.

My advice is don’t rush toward general solutions in an effort to solve problems you don’t have.

The UI isn’t coupled to the database design in any way. You could implement any type of UI on top of either database design alternative, such as:

Primary Secondary Operations
Phone:
Tollfree:
Mobile:

Or:

Phone Type Phone Number Operations

(My opinion is that the first UI option above is superior to the second UI option, it’s also exceedingly easy to implement with the “fat” customer table.)

Here are some implementation exercises to compare the difficulty of working with the database options:

  • given a customer ID get the primary tollfree number or the primary phone number if there’s no tollfree number on record
  • for each customer, fix the phone number records so that the secondary contact option is promoted to the primary contact option if there isn’t already a primary option on record, for each phone type (phone, tollfree, mobile).
  • implement user interface option A above
  • implement user interface option B above
  • create a view to present the “fat” customer contact options as a table like in the “skinny” t_skinny_customer_phone table
  • create a view to present the “skinny” customer contact options as a table like in the “fat” t_fat_customer table
  • enforce a business rule which says that not more than two contact options for each phone type should be stored in the database for each customer
  • check for duplicate contact phone numbers and remove them when found
  • put the customer contact information into a spreadsheet and email to marketing by COB