Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2010
    Posts
    12

    Post Unanswered: Help with Database Design

    So I want to create a big database that keeps track of a lot of stuff. One thing I want to put in there is my dreams. So I have the following table:

    create table Event
    (
    EventID int identity(1,1) primary key,
    PostDateTime smalldatetime not null,
    Created smalldatetime not null,
    LastModified smalldatetime not null,
    SmIcon varbinary(max) null,
    LgIcon varbinary(max) null,
    CategoryID tinyint not null default 0 foreign key references Category(CategoryID),
    constraint U_Event_EventID_CategoryID unique (EventID,CategoryID)
    )

    and another table that keeps track of the type of Event:

    create table Category
    (
    CategoryID tinyint not null primary key,
    Title nvarchar(32) not null,
    Description nvarchar(64) null,
    SmIcon varbinary(max) null,
    LgIcon varbinary(max) null
    )

    Then I have a table for the dreams:

    create table EventDream
    (
    EventID int not null primary key,
    WhenStart smalldatetime null,
    WhenEnd smalldatetime null,
    CategoryID tinyint not null
    )

    I want to make a constraint that says that if I have an entry in Event with a value of CategoryID that corresponds to a dream, that there would be a matching entry in EventDream (by EventID). How would I go about doing so? I'm a beginner to SQL so please try to keep your answers simple and not too complicated.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Remind me - did we cover this once before or did I dream it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Posts
    12
    Nice. We covered another part of the same database that I'm (still) trying to develop. It was about making different tables for each of the different categories an Event could belong to. Now I want to make sure that there is a corresponding entry present in whatever table it's supposed to be in. (An entry in Event should have one and only one matching row in another table, and should not be "orphaned").

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks
    I knew it was familiar but wasn't sure we had covered this exactly.

    This isn't strictly possible with SQL. I think it might be in relational theory.

    If you think about it you need a check on creation of an event that there is a related row in Event Dream. But the event hasn't been created yet so there has not been time to insert the event dream row.

    In other words, you require that rows are inserted simultaneously into two tables, and SQL Server can't do that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2010
    Posts
    12
    I can't just link access to my SQL database and make a form that references values from both tables and insert them both at the same time?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You said you wanted to create a constraint - what you describe now is not a constraint.

    Strictly the answer is no, but it depends on exactly what you mean. You cannot insert two rows in to different tables at the same instant. However, you can insert one immediately after the other more quickly than a person could perceive it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Don't forget to wrap your statements in a transaction (so that they either both succeed or neither do).
    Code:
    BEGIN TRAN
      <statement_1>
    
      <statement_2>
    COMMIT TRAN
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think that might confuse the poor guy even more than I am currently managing

    If he's using Access forms then T-SQL transactions won't really apply.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2010
    Posts
    12
    Right now, I'm using SQL Server Management Studio, T-SQL, and Access to update data, but I hope to eventually get to making my own front-end app. What would you guys suggest in this case? I don't have to use a constraint - I just want to find out something that works.

Posting Permissions

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