Wednesday, March 21, 2012

Problem with 2 tables

So i have these 2 tables:

Cars - has a primary key car

Car Accessories - has a foreign key car

When i insert a new car i has no accessories, so i dont have to insert a record into the car accessories table, but when
i want to add a car accessories record for a specific car i bump into a problem. If i want to add it i dont know which statement to use. If i use insert and the accessories record is already present, then ill get an error or if i use update and the accessories record is not present then it will not work again.

any ideas? maybe it is possible to use some kind of relationship which automatically creates the car accessories record when you insert a new car?

In SQL Server I use an Stored Procedure called xxx_AddUpdate, where xxx is some table or other friendly descriptive name

using the keys passed as parameters, I perform process as follows

if(exists(select <your Id> from [Car Accessories] where <your Id> = @.<your id> ))

-- perform update SQL

else

-- perform insert SQL

|||

Hey thanks for the reply, what you said will do the job just fine, i didnt know you can check if a record exists like that, thanks

I have some other question:

I have most of my sql commands stored in a xsd dataset, would there be any advantage of using stored procedures instead?

|||

For security and other reasons it is traditional to use stored procedures, unless you have other overriding factors in your design.

SP's provides a data access "protocol" that can be better managed from a security perspective, both in terms of what can be limited or exposed to a user for a process, but also in terms of limiting access by database and application roles

|||

Aah so u are saying if security is not a priority its not so important to use stored procedures.

In this case i am gonna stick the strongly typed (xds) dataset and the commands inside it, even though it has many flaws in my opinion, like for example having to make an instance of a dataset table adapter to use its commands, i find it just unnecessary.

|||

It is still worth implementing the suggested workflow in some of your projects as this gets you used to industry standard practice

No comments:

Post a Comment