Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Thursday January 23, 2003
06:59 PM

Weird Normalization Issues

[ #10149 ]

Creating a PostGresSQL Database and winding up with some unusual issues and I'm curious who else might have experience with this.

I'm building an inventory management system and we have three tables that I'm trying to figure out how to handle qw(customer country state). A country might have states, it might not. For a given customer's address, if their country does not have states/provinces (Monaco, perhaps?), what do I do? I can't insert a NULL value on a field with a foreign key constraint. I suppose I can create a blank field in the state table and reference that, but then I'm treating this field as "special" and I'm concerned that I'll need to hardcode that ID into my application. How is this usually handled where either one needs to have an FK constraint or NULL?

I've also realized that for a given customer, there's no way to stop the database from having a state of "California" and a country of "France." The interface doesn't allow this to occur, but that doesn't guarantee that future bugs won't occur. I suspect a mistake in my database design, but for the life of me, I can't figure out what I might have done wrong. Or it's possible that this is simply a situation that has to be handled on the application level :(


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Weird Normalization Issues 4 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • Depending on how the constraint is set up in the create table clause, foreign keys can be null. They need to exist in the other table if they are non-null. See the create table statement [postgresql.org] in the PostgreSQL docs[postgresql.org].

    • From what I can figure out, I apparently had NOT NULL on my column definition. That threw me off.

      What I'm probably going to do is pull the reference to the country out of the customer table and use the state to look it up. However, this means we can't have NULL states, so I'll have to create "blank" states that refer back to the country. It's ugly, but this seems to be the cleanest way.

    • Looking again at the docs, 'MATCH PARTIAL' is not implemented yet, so you still have your California/France problem. Otherwise you would be able to have one constraint on country and another on a country/state combination.
  • Postgres, like all SQL DBMS's, is very broken, but here's the "perfect world" version (where Postgres is actually a real relational DB. haha). You can probably extrapolate to an approximate real world version from here ;)

    Customer
      -----------
      customer_id   integer,
      name          text,
      country_id    integer,
      state_id      integer,

    Actually, those integers should be separate data types, but Postgres doesn't reall

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.