The entities I have described in this post are just examples to illustrate the problem. In reality there are no containers, items or packets.

I'll continue to use those entity names...

The only thing that does exist is the three table model (Container, Item, ContainerItem).

Packet, PacketItem and ContainerPacket tables do not yet exist. My plan for the primary key for...

Thank you for all the replies. Your code will work once the packets and container packets are created as you say. However, I think the difficult part is creating the...

Yes, exactly. Assuming the constraint mentioned in your first post is in place.

You are correct. Assuming that the constraint will be there, any thoughts on how to approach the problem?

Hi Gail,

Thanks for the reply. I found the KB article for SQL Server 2000. I am using SQL Server 2005. I would guess that it also applies.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273586">

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273586

Near the...

Thanks for the responses. I looked at EAV a few months ago and it was an absolute mess. It performs searches for an entire entity decently but having searches with...

Hi,

Thank you for your response. I tried to use the food example as a simplified means of explaining my issue, perhaps I simplified it too much.

My app will have...

Ah yes! I do see the difference. Thanks for the idea. I really appreciate your replies.

I am debating between this and the BIG IF at the moment. Another related...

Thank you for your replies and sorry for my delayed response.

Your idea of the dynamic switch type statement is an interesting one. I will look into this further. Thank you...

Hi,

Thank you for your reply. I agree with many of your ideas.

Your understanding of my Case 1 is correct. The specific types do return different row types but I do...

Thank you all for the comments.  Your responses have been very helpful.  I am going to look into the permanent table idea because I would rather use triggers instead of...

Hi all,

Thanks for all the replies.  It's beginning to sound as though I should use a stored procedure to update my audit table. I was really hoping to avoid this...

Hi Jeff,

Thanks for the comments.  I seemed to have confused you about where I am getting the windows username.  Allow me to clarify.

I get the windows username from the client...

Thanks for the reply.

Perhaps I haven't explained things as clearly as I should have.

When we ship our app, the database will be blank and the audit trail will not be...