question about database design

I have a table, named car. It normally have 10000 records and will growth in
the furture.
Administrator can delete the car record through an web based administrator
platform
And we not like delete the car record physically.
My question is that, what is the best method to keep the database
performance.
1. add additional field, which called is_deleted, in the car table, and mark
it as 'T' if the record is deleted, and 'F' when the record is active.
2. create new table, which have the same as schema as car table, and named
deleted_car, when administrator delete a car record, the program will remove
a record from car table and insert it into the deleted_car table.
Thanks~I prefer the first one as there is no need for additional table
Madhivanan|||> My question is that, what is the best method to keep the database
> performance.
Make sure that you have properly designed database.
Make sure that you have properly defined indexes on the tables.
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
> I have a table, named car. It normally have 10000 records and will growth
in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||Either method will work. For a table that small either method will work
well.
If the table starts to get into the hundreds of thousands of records you
will see a performance improvment (small) by using two tables. If you get
into the millions of records it will start to actuall make a difference...
This is assuming that you are using indexes correctly. If you are not, then
having 2 tables will have a large speed improvement even with the smaller
record sets.
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I have a table, named car. It normally have 10000 records and will growth
>in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
> mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
> remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||The second option might cause you some maintenance headaches down the
road... If you update your application you have to make sure you apply
updates to both sets of tables, your application will need to be modified to
work with the changes in both sets of tables, etc. What if someone
accidentally "deletes" a record and has to bring it back/make it active
again? What if, somehow, a single item gets copied to both tables?
For 10,000 -- or even 100,000 -- records I don't think it would be worth the
hassle to split it up.
Thx
Mike C
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:ujpjMOXHFHA.560@.TK2MSFTNGP12.phx.gbl...
>I have a table, named car. It normally have 10000 records and will growth
>in
> the furture.
> Administrator can delete the car record through an web based administrator
> platform
> And we not like delete the car record physically.
> My question is that, what is the best method to keep the database
> performance.
> 1. add additional field, which called is_deleted, in the car table, and
> mark
> it as 'T' if the record is deleted, and 'F' when the record is active.
> 2. create new table, which have the same as schema as car table, and named
> deleted_car, when administrator delete a car record, the program will
> remove
> a record from car table and insert it into the deleted_car table.
> Thanks~
> --
>|||right~~|||hehe~~
right...|||thanks~
^.^|||thanks~
you made my mind clear again~|||>> 1. add additional field [sic], which called is_deleted, in the car
table, and mark it as 'T' if the record [sic] is deleted, and 'F' when
the record [sic] is active. <<
Rows are not records; columns are not fields; an RDBMS is not a
sequential file system. The basic idea is right, but make this
attribute a general status code that can take other values or make it a
timestamp so you know when the event occurred.