Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2008
    Posts
    1

    One column referencing multiple tables

    Hi,

    I am facing the following challange: for a university project
    I have created a database. One of it's tables shall store logging
    data (for example when a app user has been created/edited or
    some other object has been created).
    The problem is, that I want to store a relation to the object which
    actually caused the log entry. Consider the following example:

    ID Date Event Object_Id
    ========================
    1 1.3.08 User_Created 1 => Referencing the users table here
    2 4.6.08 Product_Created 2 => Referencing prod. table here

    So I want to reference multiple tables from one column.
    How can I do this in the most efficient way? I am using SQL Server 2008 and LINQ to SQL.

    Many thanks in advance.
    Robert

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You use the word relation - you mean relationship right?

    You want a bucket to log all changes in all tables (or more than one table anyway) and you want to store the pk of the changed rows, and you want relational integrity? That dog won't hunt, Monsignor.

    One thing you haven't covered is what you want to happen if a row is deleted - if you want a log of that too then not only can you not physically implement relational integrity, you aren't even conceptually working with relational integrity.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Not much to go on but here's an example that may help:

    CREATE TABLE YourTable
    (ID INT NOT NULL PRIMARY KEY,
    Date DATE NOT NULL,
    Object_Id INT NOT NULL REFERENCES Objects (Object_Id));

    CREATE TABLE Users (Object_Id INT NOT NULL REFERENCES Objects (Object_Id), ...);

    CREATE TABLE Products (Object_Id INT NOT NULL REFERENCES Objects (Object_Id), ...);

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice one, dportas, that's elegant and simple

    looks like the dog hunts just fine

    straightforward application of supertype/subtype concept

    requires multiple LEFT OUTER JOINs to retrieve details, though

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Full price for gum?

    I misunderstoodulated the problem

    Let's see if I understand these things right now: users and products should have check constraints to ensure the value of object_id can be one value only. So object_id is not surrogate. Do you guys like columns with one value and one value only in them? Should we call the tables tblObjects, tblProducts, tblUsers etc?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...I'm also not quite sure of the value of what is being recorded. Without a reference back to the pk of the created row I don't really see what you can do with the information.

    Quote Originally Posted by databaseLog
    Attention: A new product has been created
    Quote Originally Posted by operator
    Oh cool - what is it?
    Quote Originally Posted by databaseLog
    ...........i'unno........
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ......and.....
    Quote Originally Posted by r937
    straightforward application of supertype/subtype concept

    requires multiple LEFT OUTER JOINs to retrieve details, though
    clearly I am being slow today as it doesn't look sub\ super to Yours Truly nor do I see where outies are required.

    To be fair it is beer o'clock in Blighty now and me noggin is feeling a bit dehydrated.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I wouldn't join based on Object_ID. That's likely to cause havoc if you need to drop and recreate the database or share data between instances, as there is no guarantee that the same object_id will be assigned from one install or restore to another.
    Link on the object name instead if you insist on enforcing relational integrity with the physical database schema.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sigh

    and because that's not 10 characters, i say again, "sigh"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bear with me on this, poots

    if you wanted to have a supertype that encompasses both users and products, at least insofar as logging changes to both types was concerned, what would you call the supertype?

    substitute your word for "object" in dportas' reply

    the log entry refers to the [yourword] which it detected was changed

    the [yourword] has a unique identifier, and it is either a user or product

    user and product are subtypes of [yourword]

    as for the LEFT OUTER JOINs, you'd want two of them, one to users and one to products, if you wanted specific details of what it was that was logged
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Aw - poor Rudy having to bear with dumb ickle me

    I'm offski for the weekend and can't elaborate now but no - I don't like the schema and I don't like the whole premise of the problem. I will throw out one thunkle to answer your question - object_id does not compose any part of any primary key other than in the object table. As such it is not sub\ super - it is a looky uppy.

    I'm also in shock because I think blindman is advocating a natural key over a GUID (or any sort of surrogate). Obviously I have not yet drunk enough booze - that will be a transient problem.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Ha ha. I thought about that as I typing it, but I figured Rudy would call me on it first.
    What's up, Rudy? Were you napping?
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have another part of my life where i'm AFK for several hours at a time

    so i like it when people fill in for me

    i just got back home, and as soon as i can slip into something more comfortable, ...

    ooops, wrong thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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