locked
Need Help: Relational Database Theory RRS feed

  • Question

  • I have created a database in SQL Server 2005 Express consisting of two tables

    tbl_tickets and tbl_messages

    tbl_tickets has a primary key called TICKETID and a foreign key field MESSAGEID which references MESSAGEID (Primary Key) contained within tbl_messages.  To create the foreign key reference (relationship between the two tables) I used the database diagrams facility.  The Primary Key fields are set as Identity Columns such that they increment without me having to include them in any future SQL Insert Statements I can open the messages table using the 'Open Table' option and insert a record; but when I go to insert a record in tbl_tickets The MESSAGEID Foreign Key is set to not null but when I insert values into the non key fields; when I hit enter it comes up saying unable to insert row etc due to the MESSAGEID field being unable to accept null values.  It is at this point that I get confused as I thought the FK field of the tbl_tickets was supposed to mirror the value of what is contained in the tbl_tickets MESSAGEID PK field.  (So that you need not worry about providing a value for the MESSAGEID FK field in tbl_tickets.

     

    The objective is to build a database containing two tables such that any one ticket can have many messages and that if a ticket is deleted all messages belonging to that ticket are deleted also (Cascade Delete). 

    Some might say just set MESSAGEID FK field in tbl_tickets to allow null values but all the example designs I have seen in my Sitepoint book use not null; which means there must be a reason for it and must mean i'm going wrong somewhere........

     

    Can anyone help me?

    Monday, April 12, 2010 8:41 PM

Answers

  • So yes, it will be an issue with a procedure that creates them both at the same time. So let's start with the basis of your tables.  I used surrogate keys in the form of identity columns because that is what you have seemingly done.  I added "natural" keys that every table should have to give users easy access to the table, usually via some formatted value that is easy to remember in the same way a phone number is, or a SSN number.  Just having a number for the only key means that if a user or (even more frequently) process goes nuts and keeps entering the same data other than the artificial value chosen for the key, it will be denied).

    create table ticket
    (
        ticketId int identity primary key,
        ticketNumber char(10) unique, --or some such key that the user knows and is formatted as they expect
        ... other columns
    )

    create table message
    (
        messageId int identity primary key,
        messageNumber char(10) unique, --or some such key that the user knows and is formatted as they expect
        ticketId int not null references ticket(ticketId)
        ... other columns
    )

    So in your process, a new ticket row is created.  Then later, the user wants to add a message to a ticket.  It would seem that you would likely be in the context of a ticket on your application, so you would have the ticketId, but if not, the user might pass the ticketNumber and retrieve the ticketId.  So you wouldn't need a transaction or a procedure (though again, it is a best practice to use procedures, even if your a just doing one operation so your access to SQL Server needn't change even if the implementation does (at least in a way that doesn't change the functionality substantially.)

    A proc like she suggested is a good way to give the user the ability to create both items together, especially if that was a natural part of your process.


    Louis

    Wednesday, April 14, 2010 5:08 AM
  • Assuming SQL Server 2005

    create procedure CreateNewTicket

    (@Message varchar(1000),

     some other parameters related to ticket)

    as

    begin

       declare @Messages table (MessageID int)

        begin transaction

           insert into Messages OUTPUT Inserted.MessageID into @Messages values (@Message)

              insert into Tickets (MessageID, some other fields) select M.MessageID, other passed parameters from @Messages M

       end transaction

    end

    ------------

    From the top of my head.

    You may also find this link helpful How to insert information into multiple related tables and return ID using SQLDataSource


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, April 12, 2010 9:30 PM
    Answerer

All replies

  • No, the MessageID is not going to be inserted automatically because of the relations. You have to supply this value in your insert statements.

    Can you please show stored procedure that inserts records into Messages table and Tickets table?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, April 12, 2010 9:19 PM
    Answerer
  • Im a beginner at SQL Server and this is my first relational DB.  I didn't realise I needed a stored procedure???

    I have a faint idea of what a SP is; though, would it be too much of a disposition of suggesting what kind of SP I would need to achieve the objective????

    Monday, April 12, 2010 9:24 PM
  • Assuming SQL Server 2005

    create procedure CreateNewTicket

    (@Message varchar(1000),

     some other parameters related to ticket)

    as

    begin

       declare @Messages table (MessageID int)

        begin transaction

           insert into Messages OUTPUT Inserted.MessageID into @Messages values (@Message)

              insert into Tickets (MessageID, some other fields) select M.MessageID, other passed parameters from @Messages M

       end transaction

    end

    ------------

    From the top of my head.

    You may also find this link helpful How to insert information into multiple related tables and return ID using SQLDataSource


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, April 12, 2010 9:30 PM
    Answerer
  • Thankyou for your swift response:  I will have a go at it tonight if it works then great I'll mark your thread response as Answered or if I don't get the chance tonight then I will review it tomorrow...

     

    Many Thanks,

     

     

    LTGoldman

    Monday, April 12, 2010 9:43 PM
  • I am not sure what your problem is. If you set the Ticket table to have a foreign key to the Message table and the reference column (messageId) is NOT NULL, then you will have to create a Message row first, then get the key of that message row (Naom's code with output works, or scope_identity() is another common solution) and use it in the Ticket Table.

    I would suggest to you that what might be an issue here is that it seems backwards.  Would you have 1 message to many tickets? Or Many messages to one ticket?  Is it the message that originates the ticket?  Or the messages that are attached to the ticket?  A clear understanding of the meaning of each table would help to suss out a design and help out your issue. If message and ticket are a 1-1 relationship, and they always get created at the same time, it isn't clear that you need two tables...Since you made them two tables, I will guess you have more in mind than that.

    >>I didn't realise I needed a stored procedure???<<

    You don't "need" one, but stored procedures are definitely the preferred manner of building data access code to most of the people who answer questions. Clearly it isn't always possible, but being able to build this kind of code in T-sql and limit transactions to occuring at the batch level is always preferable.


    Louis

    Tuesday, April 13, 2010 4:06 AM
  • Sorry I thought I had answered this in the second paragraph; but for further clarity:

     

    The relationship between tickets and messages is 1 to many i.e. one ticket many messages.  There will be more than 1 ticket in tbl_tickets obviously.  Basically its part of the backend for an IT Support Desk application.  Tickets = Support Calls, messages belong to tickets.

    Situation: user raises ticket by filling out form.  Results of which are stored in tbl_tickets.  At a later point in time the user might want to send a message regarding the ticket they raised which is stored in tbl_messages.  After a while admin might want to either delete a ticket or close it which is why tickets need to be related to messages as if a ticket is deleted I would want all related messages for the ticket to be removed also.......

     

    I hope this helps for clarity............

    Tuesday, April 13, 2010 3:38 PM
  • Will the possible time difference between the illicitation of a ticket and a message be a problem i.e. I don't want to fire a strored procedure straight away after creating a ticket it could be days that go past without a messgage being generated and some tickets may not receive any messages at all (its up to the end user).  Is the answer to this: you call the SProcedure as in when you need to create a message?

     

     

    Tuesday, April 13, 2010 4:10 PM
  • So yes, it will be an issue with a procedure that creates them both at the same time. So let's start with the basis of your tables.  I used surrogate keys in the form of identity columns because that is what you have seemingly done.  I added "natural" keys that every table should have to give users easy access to the table, usually via some formatted value that is easy to remember in the same way a phone number is, or a SSN number.  Just having a number for the only key means that if a user or (even more frequently) process goes nuts and keeps entering the same data other than the artificial value chosen for the key, it will be denied).

    create table ticket
    (
        ticketId int identity primary key,
        ticketNumber char(10) unique, --or some such key that the user knows and is formatted as they expect
        ... other columns
    )

    create table message
    (
        messageId int identity primary key,
        messageNumber char(10) unique, --or some such key that the user knows and is formatted as they expect
        ticketId int not null references ticket(ticketId)
        ... other columns
    )

    So in your process, a new ticket row is created.  Then later, the user wants to add a message to a ticket.  It would seem that you would likely be in the context of a ticket on your application, so you would have the ticketId, but if not, the user might pass the ticketNumber and retrieve the ticketId.  So you wouldn't need a transaction or a procedure (though again, it is a best practice to use procedures, even if your a just doing one operation so your access to SQL Server needn't change even if the implementation does (at least in a way that doesn't change the functionality substantially.)

    A proc like she suggested is a good way to give the user the ability to create both items together, especially if that was a natural part of your process.


    Louis

    Wednesday, April 14, 2010 5:08 AM