Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32

    Question Unanswered: Record (entity) versions...

    Database newbie question:

    My sample database (T-SQL syntax):

    CREATE DATABASE sample
    GO
    USE sample
    CREATE TABLE customers (CustomerId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,name varchar(50),address varchar(50))
    CREATE TABLE invoices (InvoiceId int IDENTITY(1,1) PRIMARY KEY CLUSTERED,date datetime,CustomerId int REFERENCES customers(CustomerId))
    INSERT customers (name,address) VALUES ('First Company Ltd.','New York')
    INSERT customers (name,address) VALUES ('Second Company Ltd.','Washington')
    INSERT invoices (date,CustomerId) VALUES ('Jan 1, 2004',1)
    INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',1)
    INSERT invoices (date,CustomerId) VALUES ('Jan 2, 2004',2)
    SELECT * FROM customers
    GO

    CustomerId name address

    1 First Company Ltd. New York
    2 Second Company Ltd. Washington

    SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId
    GO

    date name address

    2004-01-01 00:00:00.000 First Company Ltd. New York
    2004-01-02 00:00:00.000 First Company Ltd. New York
    2004-01-02 00:00:00.000 Second Company Ltd. Washington

    UPDATE customers SET address='Boston' WHERE name='First Company Ltd.'
    GO
    INSERT invoices (date,CustomerId) VALUES ('Jan 3, 2004',1)
    SELECT * FROM customers
    GO

    CustomerId name address

    1 First Company Ltd. Boston
    2 Second Company Ltd. Washington

    SELECT i.date,c.name,c.address FROM invoices AS i INNER JOIN customers AS c ON i.CustomerId=c.CustomerId
    GO

    date name address

    2004-01-01 00:00:00.000 First Company Ltd. Boston
    2004-01-02 00:00:00.000 First Company Ltd. Boston
    2004-01-02 00:00:00.000 Second Company Ltd. Washington
    2004-01-03 00:00:00.000 Second Company Ltd. Boston

    Is it possible in any of the RDBMS's to make this last query return the following result set?

    2004-01-01 00:00:00.000 First Company Ltd. New York
    2004-01-02 00:00:00.000 First Company Ltd. New York
    2004-01-02 00:00:00.000 Second Company Ltd. Washington
    2004-01-03 00:00:00.000 First Company Ltd. Boston
    (sz)Tomi

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not with the tables you have. An RDBMS will only work on existing data. If you want a history of record values then you will need to store that, either in the same table as current records using an AsOf date, or in a separate history table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32
    I just want to know, if exists a feature in any of the today's DMBS's to manage the entity (record) changes or history without (sql)programatically adding history tables.
    (sz)Tomi

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    No, there does not. You will need to make history tables, or redesign your current tables so they have active/inactive records.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How can a change of address which is an ATTRIBUTE have any bearing on the change that you expect of primary key being CustomerID? Based on your expectations CustomerID is an attribute and Address is the driving force, which I think contradicts any definition of an entity. You need to start with the basics (I am not trying to sound like I know it all, but it's true) of a relational database design before you jump into attempting to select an DBMS.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •