Re: Primary key on one or two fields

I'm basically creating a structure from scratch (I'll convert the old data as required).

I'm trying to fully seperate the data into its three components, hence three tables. This makes it easier to code the front end and ensure I don't end up with invalid data (e.g. a rate_id and ratename that don't match up between different rates). I am more interested in correctness rather than speed here, which is unlikely to be much of an issue. I thought the days of flat file databases were long gone...

OK, this is my current proposed structure:

Create Table Rates (Rate_Id Int Identity(1,1), RateName VarChar(20) Not
Null Default '', RateType Int Not Null Default 1, Constraint PK_Rates Primary Key (RateId))

Create Table RateRevisions (Revision_Id Int Identity(1,1), Rate_Id Int not null, RateDate DateTime not null, Constraint PK_RateRevisions Primary Key
(Revision_Id))

Create Table RateRevisionEmployees (RateRevision_Id Int not null, Employee_Id
Int not null, Rate Float not null, Constraint PK_RateRevisionEmployees Primary Key
(Revision_Id, Employee_Id))

If I want to see current rates I can use Select RateName, EmployeeId
From Rates
Inner Join
(Select * From RateRevisions Where RateDate=(Select max(RateDate) From RateRevisions RR Where RR.RateId=RateRevisions.RateId)) --Gives only latest revisions
on Rates.Rate_Id=RateRevisions.Rate_Id
Inner Join RateRevisionEmployees On
RateRevisions.Revision_Id=RateRevisionEmployees.Revision_Id

The alternative is:

Create Table Rates (RateId Int Identity(1,1), RateName VarChar(20) Not Null Default '', RateType Int Not Null Default 1, Constraint PK_Rates Primary Key (RateId))

Create Table RateRevisions (RateRevisionId Int, RateId Int, RateDate DateTime, Constraint PK_RateRevisions Primary Key (RateRevisionId, RateId))

Create Table RateRevisionEmployees (RateId, RateRevisionId Int, EmployeeId Int, Rate Float, Constraint PK_RateRevisionEmployees Primary Key (RateId, RateRevisionId, EmployeeId))

I would add the obvious forign keys.

I was under the impression that getting the database to generate my unique keys was a good thing, especially in a multi-user environment, and I can't say I understand your reasons for not liking them other than not being standard. I had previously been using the method of: begin transaction
put (select max(id)+1 from rates) into a variable insert into rates (id...) values (my variable above, other values...) commit transaction
However this only works with sequential transactions so is no good with Oracle.

joe.celko_at_northface.edu (--CELKO--) wrote in message news:<a264e7ea.0308061450.4f9e1a9b_at_posting.google.com>...
> >> I currently have the following information: <<
>
> Want to share it with us? Please post DDL, so that people do not have
> to guess what the keys, constraints, Declarative Referential
> Integrity, datatypes, etc. in your schema are.
>
> From the narrative, is sounds like you meant to have a table for Rates
> and their history, but someone split out the attributes of these rates
> into too many talbes, thus destroying the data model for them. Try
> this:
>
> CREATE TABLE RateHistory
> (rate_id INTEGER NOT NULL, -- standard industry code?
> rate_name CHAR(15) NOT NULL,
> rate_type CHAR(1) DEFAULT '?' NOT NULL
> CHECK(rate_type IN (...)),
> rate FLOAT NOT NULL,
> effective_date DATE NOT NULL,
> termination_date DATE, -- null means current rate
> CHECK (effective_date <= termination_date),
> ...,
> PRIMARY KEY (rate_id, effective_date));
>
> if there is an industry standard code for the revisions, then use it
> in this history table. This VIEW might be handy:
>
> CREATE VIEW CurrentRates (..., termination_date)
> AS SELECT ..., CURRENT_TIMESTAMP
> FROM Rates
> WHERE termination_date IS NULL;
>
> >> First: Each rate has a unique RevisionId (I can get the database
> to generate this using autonumber/identity). <<
>
> No, IDENTITY, GUID, ROWID and other proprietary, non-relational crap
> are **by definition** not keys. At best, they are redundant in a good
> data model and since there is no way to verify them against the
> reality of the data model, you can kiss data integrity goodbye.
>
> >> ..minimum number of bits of information to define the record [sic]
> (the RevisionIds will be smaller). <<
>
> You're still thinking in terms of physical file structures and not
> logical models. You even use the words "record" and "field", like
> this was a file system and want a sequential record number in it.
>
> Who cares about the size yet? Get the model right and then worry
> about the PHYSICAL storage and speed. Hey, if the data does not have
> to be right, I can make it really fast :)
>
> >> It would also allow me to join the RateRevisionEmployees table
> directly to the Rates table. <<
>
> What is the meaning of this table in your data model? I did not
> understand that at all.
Received on Thu Aug 07 2003 - 13:02:04 CEST