Re: [GENERAL] Design

My advice is to make the schema a little simpler.
Break out the Auction's vin-char(17) into different fields, with one field
being related to the BlackBook's vin-char(8).
  Auction
  vin-char(8)
  other-field(..)
  other-field2(..)
  AuctionId

Then you can do an outer join between the two tables by using the following:
  ... where  blackbook.vin-char = auction.vin-char ...

If you want to get the Auction's different fields together, just append the
fields together.

Hope this helps
-----Original Message-----
From: R.Stevens <raimee@ij.net>
To: Postgres-General <pgsql-general@postgreSQL.org>
Date: Thursday, August 06, 1998 9:15 PM
Subject: [GENERAL] Design


>I really only need to start with a good plan.  I have the following
>problem:
>
>I get data from two text files.  The first file contains BlckBook data
>on all vehicles.  It contains fields such as vin, make, model, year,
>etc...
>The second file contains vehicle data specific to actual vehicles sold
>on an auction.  Some fields in this file are vin,  amount the vehicle
>auctioned for, Auctioners ID, etc...
>
>BlackBook                Auction
>-------                -----
>vin -char(8)              vin-char(17)
>year -char(1)           AuctionId
>make
>model
>
>etc....
>The files are related by the following:
>BlackBook.vin + BlackBook.year == Auction.vin(1-8) + Auction.vin(10)
>That  is, the BlackBook vin field and year field match the Auction vin
>field characters 1 through eight plus character ten.  Character nine is
>skipped.
>
>I need to access the Black Book Data (fields including make, blackbook
>value, etc..) for a given vehicle in the Auction File.   I am not sure
>of the best way to accomplish this.
>
>Eventually I will have approx. 200K records in the Auction file and
>about 82K records in the BlackBook file.
>
>I see only one option:
>
>1)  Build a third table (or view, Although multiple views will be needed
>later to establish levels of authorization).
>This third table can be construced by reading records from the Auction
>File (table), matching a valid vin number and then selecting required
>fields from both
>tables and copying them to the New table.   My problem here is with the
>implementation.  Do I use a subquery?  Or a function?  Not sure how to
>build the function !
>
>I need some help here.  Any suggestions??
>
>