Re: indexes - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

True.  That doesn't mean I like it...

Real-life example: We design and run customer service centers for
many toll roads in the US Northeast.

So, we have a set of tables like this:

T_AGENCY (
AGENCY_ID    INTEGER  PRIMARY KEY,
AGENCY_CODE  CHAR(2),
AGENCY_NAME  CHAR(40),
etc,
etc );

T_PLAZA (
PLAZA_ID          INTEGER PRIMARY KEY,
EXTERNAL_PLAZA_ID CHAR(5),
PLAZA_NAME        CHAR(40),
AGENCY_ID         INTEGER FOREIGN KEY REFERENCES T_AGENCY.AGENCY_ID,
etc,
etc );

T_LANE (
LANE_ID            INTEGER PRIMARY KEY,
EXTERNAL_LANE_ID   CHAR(5),
LANE_NAME          CHAR(40),
PLAZA_ID           INTEGER FOREIGN KEY REFERENCES T_PLAZA.PLAZA_ID,
etc,
etc);

So, in our various transaction and summary tables, we have the
LANE_ID column instead of natural key AGENCY_CODE,
EXTERNAL_PLAZA_ID, EXTERNAL_LANE_ID.

Since we have multiple projects, each with their own "database", and
each database is federated (because they are so large), each actual
database must keep a copy of T_AGENCY, T_PLAZA & T_LANE.  The
agencies pass transaction data around, since they all use the same
brand of toll tag, and motorists can use their tag at any other
agency's lanes and have it "work", so if the synthetic keys of these
tables were to ever get out of sync, Bad Things would happen.


On 11/24/06 18:42, Ben wrote:
> Yes, it does. So of course it depends on how you use it to know what's
> going to be more efficient. For instance, if the rows in this table
> contain strings of more than a few bytes, and more than a couple tables
> reference this table with a foreign key, then you will quickly start to
> save space by using a numeric primary key, even if it is an artificial
> construct.
>
> For the kind of work I find myself doing, it's rare that it would be
> more efficient to not have the artificial construct. But that doesn't
> mean one is always better than the other.
>
> On Nov 24, 2006, at 11:14 AM, Ron Johnson wrote:
>
> But that requires that you haul an artificial construct around.
>
> On 11/24/06 12:38, Ben wrote:
>>>> It depends how it's going to be used. If you are going to reference this
>>>> table in other tables a lot and/or rarely care about what the name
>>>> actually is, then the two-column approach is going to be more efficient.
>>>> Numbers are smaller and easier to compare than strings.
>>>>
>>>> On Nov 24, 2006, at 6:54 AM, Tom Allison wrote:
>>>>
>>>>> I notice a lot of places where people use the approach of creating an
>>>>> index and a unique key like:
>>>>>
>>>>> CREATE TABLE foo (
>>>>>   idx SERIAL PRIMARY KEY,
>>>>>   name varchar(32) UNIQUE NOT NULL
>>>>> )
>>>>>
>>>>> instead of
>>>>> CREATE TABLE foo (
>>>>>   name varchar(32) PRIMARY KEY
>>>>> )
>>>>>
>>>>> If the name is NEVER going to change, is there any advantage to doing
>>>>> this?
>>>>> If there are many-to-many reference tables (like name-to-friends) is
>>>>> this any different?
>>>>>
>>>>> I've seen this a lot, but I've always assumed that with the condition
>>>>> that 'name' would NEVER change, there was no advantage.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD4DBQFFZ69TS9HxQb37XmcRAgAAAJUT1HnH/ocUKLxbow6GAg2Gz1wpAJwPQuJV
ZodGoV0BF2NgKn2YTrEoSQ==
=Y+x1
-----END PGP SIGNATURE-----