How to let users modifiy this ...?

Then you'll have to have then define what is a correct status and change it
accordingly. If all they want to do is change the name from Pending to
Standby, that's just a quick update to the Status table, with no impact to
the Orders table.
Alternatively, you can add the new status to the status table and then
update the orders table:
update Orders
set
StatusID = 3
where
StatusID = 2
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:C8737DE7-4B39-4C66-B3BD-C9494205158C@.microsoft.com...
1. Order must have a Status,
2. Order table is linked to the Status table via a FK (PK in Status table),
3. Users want to be able to change (add [no problem], modify [no problem]
and delete [big problem]) definition of what Status is.
4. For example (Status table and then Order table)
StatusID =1 StatusName = New
StatusID =2 StatusName = Pending
OrderID=3 StatusID=1
OrderID=4 StatusID=1
OrderID=5 StatusID=2
What users want to do is this "'Pending' order status is not a valid status
anymore, I want to delete it from the Status table". Deletiing StatusID=2
from the Status table is problematic since each order must have some sort of
status + behavior (what other Status to default to in this case) is not even
defined by the client.
Is there a way to deal with this?Tom,
You are right, it can be done the way and that's what I was trying to
explain to the users but the insist on "deleting" a status and in their worl
d
this means that if today there are 5 different order statuses available and
they want one removed then (physically) there are going to be only 4
available after removal.
When I ask them "what do you want to do with all the orders that now point
to a status which doesn't even exist in your system" they say "well, you
figure it out". Round and round we go.
There is no way one could even remove it since this would violate the
integrity and database would never allow this transaction to complete.
"Tom Moreau" wrote:

> Then you'll have to have then define what is a correct status and change i
t
> accordingly. If all they want to do is change the name from Pending to
> Standby, that's just a quick update to the Status table, with no impact to
> the Orders table.
> Alternatively, you can add the new status to the status table and then
> update the orders table:
> update Orders
> set
> StatusID = 3
> where
> StatusID = 2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Art" <Art@.discussions.microsoft.com> wrote in message
> news:C8737DE7-4B39-4C66-B3BD-C9494205158C@.microsoft.com...
> 1. Order must have a Status,
> 2. Order table is linked to the Status table via a FK (PK in Status table)
,
> 3. Users want to be able to change (add [no problem], modify [no problem]
> and delete [big problem]) definition of what Status is.
> 4. For example (Status table and then Order table)
> StatusID =1 StatusName = New
> StatusID =2 StatusName = Pending
> OrderID=3 StatusID=1
> OrderID=4 StatusID=1
> OrderID=5 StatusID=2
> What users want to do is this "'Pending' order status is not a valid statu
s
> anymore, I want to delete it from the Status table". Deletiing StatusID=2
> from the Status table is problematic since each order must have some sort
of
> status + behavior (what other Status to default to in this case) is not ev
en
> defined by the client.
> Is there a way to deal with this?
>|||Add a status column to the table with values of "Active" and "Inactive".
Since your table is called Status already, lets call this new column
ActiveInactive
Allow them to select active rows in the application, but not innactive rows.
The existing inactive rows will still be joined in queries, and can show up
on reports so you don't lose that fact that the code was valid at one point.
You can take it to a next level and put a date in your table indicating when
the ActiveInactive flag (or the description) changed.
"Art" <Art@.discussions.microsoft.com> wrote in message
news:0A001470-14A7-43FB-AC7E-1805AA425FB6@.microsoft.com...
> Tom,
> You are right, it can be done the way and that's what I was trying to
> explain to the users but the insist on "deleting" a status and in their
world
> this means that if today there are 5 different order statuses available
and
> they want one removed then (physically) there are going to be only 4
> available after removal.
> When I ask them "what do you want to do with all the orders that now point
> to a status which doesn't even exist in your system" they say "well, you
> figure it out". Round and round we go.
> There is no way one could even remove it since this would violate the
> integrity and database would never allow this transaction to complete.
> "Tom Moreau" wrote:
>
it
to
table),
problem]
status
StatusID=2
sort of
even|||And who did it - that'll be the next question...
"Jim Underwood" wrote:

> Add a status column to the table with values of "Active" and "Inactive".
> Since your table is called Status already, lets call this new column
> ActiveInactive
> Allow them to select active rows in the application, but not innactive row
s.
> The existing inactive rows will still be joined in queries, and can show u
p
> on reports so you don't lose that fact that the code was valid at one poin
t.
> You can take it to a next level and put a date in your table indicating wh
en
> the ActiveInactive flag (or the description) changed.
> "Art" <Art@.discussions.microsoft.com> wrote in message
> news:0A001470-14A7-43FB-AC7E-1805AA425FB6@.microsoft.com...
> world
> and
> it
> to
> table),
> problem]
> status
> StatusID=2
> sort of
> even
>
>|||very true.
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:C35DFC91-892D-40D5-8DA9-1EC2FE6A305E@.microsoft.com...
> And who did it - that'll be the next question...
> "Jim Underwood" wrote:
>
"Inactive".
rows.
up
point.
when
their
available
point
you
change
to
impact
then
not