Re: "Smart" Product Codes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 10/15/04 

Date: Fri, 15 Oct 2004 13:59:07 -0700

This is not a good idea but try creating a UDF and use this functions to
create the calculated column. Pass four parameters (4 keys) and create the
composite value.

AMB

"Mike Labosh" wrote:

> > If I understood, then you have something like:
> Yes, that's exactly what I have.
>
> > Could you post which columns are the pk of table Product?
> ProductKey
>
> > Could you post the fk constraints defined in table Product?
> VendorKey, OperatingSystemKey, etc.
>
> All of the identity columns are the PK of their respective tables.
>
> > if you have all four natural keys and also an IDENTITY column, why do you
> > want to create a new column that is the concatenation of the four natural
> > ones?
> > How do you plan to use the "smart product code" in your model?
>
> The pointy haired bosses on the "standards committee" hath said from on
> high: "Every table must have an identity column as its sole primary key"
> And it has worked well just about every place in the system. In this
> example though, using the natural keys would make things less complex. But
> I can't change it like that.
>
> In many programs, the 4 natural keys of the 4 lookup tables are used. The
> composite of those four natural keys represents the natural key for the
> Product table, and is passed to external black box systems, and also has
> some significance to our clients.
>
> Any time a program needs to get the whole composite key, it has to join all
> five of these tables to get the four little natural keys and concatenate
> them using ProductKey in the WHERE clause. I thought that having a
> calculated column would make that a lot less painful.
>
> I don't know exactly how they use them, but it is mostly in join's, where's
> and orderby's. I'm not even a DBA either. I'm just the badass .net guy
> they brought in to fix a giant data importing system (which I have now
> inherited from a "fired" consulting firm). Unfortunately, all the code I
> have worked on has been b0rken and I have had to rewrite most things from
> scratch. Sadly, the consulting firm did a bad job of the database design,
> and I have accidentally become the defacto DBA in this migration.
>
> --
> Peace & happy computing,
>
> Mike Labosh, MCSD
>
> "It's 4:30 am. Do you know where your stack pointer is?"
>
>
>

Relevant Pages

  • Re: Data Primary key vs. Artificial (Autonumber) primary key
    ... "natural" keys, I use unique indexes. ... the message about having table constraints on the candidate keys ... but I would wager than most users in the 'Autonumber PK' camp put ... worse) with multi-column natural keys (I have no objection on ...
    (microsoft.public.access.tablesdbdesign)
  • Re: "Smart" Product Codes
    ... > This is not a good idea but try creating a UDF and use this functions to ... > create the calculated column. ... Pass four parameters (4 keys) and create the ... five-way join to build the composite thing and then see about indexing the ...
    (microsoft.public.sqlserver.programming)
  • Re: Fixing up structure with primary, natural, surrogate, etc.
    ... So, I re-did most of my tables, creating natural keys, setting unique ... If you already have the unique index, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Access 2010 beta - Publish to Sharepoint
    ... talking only about junction tables and the seriousness of the ... The ability of having compound keys is also good. ... Multicolumn indices are indeed convenient when natural keys are ...
    (comp.databases.ms-access)
  • Re: Why use a composite PK ever?
    ... such as here in Denmark where each citizen ... operator simply enters incorrect birthdate ... So after such cases I'm very sceptical about natural keys as primary ...
    (comp.databases)