Re: simple design choice
If you want to choose the first one, then the flag' data type must be enum.For int fill the disk with 4 byte and enum just 1 byte. On Sat, Oct 4, 2008 at 2:15 AM, Alex K [EMAIL PROTECTED] wrote: That seems like a nice trick. I suppose the flag would just be an int and not an enum in this case. 2008/10/3 Mr. Shawn H. Corey [EMAIL PROTECTED]: On Fri, 2008-10-03 at 09:58 -0700, Rob Wultsch wrote: On Fri, Oct 3, 2008 at 9:49 AM, Alex K [EMAIL PROTECTED] wrote: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex #1 Define uses a lot of unnecessary space. I would imagine it would add not much more than 1 MB to the size of db, depending on column choice. A decent choice I think. #2 Yuck. #3 A compact and clean solution. If you're going to do #1, make the new column status, with two states: active and deleted. In the future you can add more states without re-doing your tables again. -- Just my 0.0002 million dollars worth, Shawn Linux is obsolete. -- Andrew Tanenbaum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
simple design choice
Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple design choice
2) is probably cleaner but a hassle as well because one needs to make sure all user_ids are also in this new table. 2008/10/3 Alex K [EMAIL PROTECTED]: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple design choice
On Fri, Oct 3, 2008 at 9:49 AM, Alex K [EMAIL PROTECTED] wrote: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex #1 Define uses a lot of unnecessary space. I would imagine it would add not much more than 1 MB to the size of db, depending on column choice. A decent choice I think. #2 Yuck. #3 A compact and clean solution. -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: simple design choice
I would say providing a table to retain the users relevant details especially after marking as deleted would be a logical choice Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Fri, 3 Oct 2008 18:49:07 +0200 From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: simple design choice Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Get more out of the Web. Learn 10 hidden secrets of Windows Live. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008
Re: simple design choice
I would do #3. On 10/3/08, Alex K [EMAIL PROTECTED] wrote: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple design choice
Hi, Personally I would do #3 as well. Have an exact copy (structurally) of your original table, when the record is deleted then move the account's details to your deleted_users table so it doesn't appear in users but you still have all the details. Not sure if it's possible in MySQL but in other database systems you can create a DELETE trigger that does this automatically when a record is deleted, so your application only has to worry about issuing one query. Andy Fish Kungfu wrote: I would do #3. On 10/3/08, Alex K [EMAIL PROTECTED] wrote: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple design choice
On Fri, 2008-10-03 at 09:58 -0700, Rob Wultsch wrote: On Fri, Oct 3, 2008 at 9:49 AM, Alex K [EMAIL PROTECTED] wrote: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex #1 Define uses a lot of unnecessary space. I would imagine it would add not much more than 1 MB to the size of db, depending on column choice. A decent choice I think. #2 Yuck. #3 A compact and clean solution. If you're going to do #1, make the new column status, with two states: active and deleted. In the future you can add more states without re-doing your tables again. -- Just my 0.0002 million dollars worth, Shawn Linux is obsolete. -- Andrew Tanenbaum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple design choice
That seems like a nice trick. I suppose the flag would just be an int and not an enum in this case. 2008/10/3 Mr. Shawn H. Corey [EMAIL PROTECTED]: On Fri, 2008-10-03 at 09:58 -0700, Rob Wultsch wrote: On Fri, Oct 3, 2008 at 9:49 AM, Alex K [EMAIL PROTECTED] wrote: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex #1 Define uses a lot of unnecessary space. I would imagine it would add not much more than 1 MB to the size of db, depending on column choice. A decent choice I think. #2 Yuck. #3 A compact and clean solution. If you're going to do #1, make the new column status, with two states: active and deleted. In the future you can add more states without re-doing your tables again. -- Just my 0.0002 million dollars worth, Shawn Linux is obsolete. -- Andrew Tanenbaum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: simple design choice
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alex K Sent: Friday, October 03, 2008 2:15 PM To: Mr. Shawn H. Corey Cc: Rob Wultsch; mysql@lists.mysql.com Subject: Re: simple design choice That seems like a nice trick. I suppose the flag would just be an int and not an enum in this case. [JS] I use a set for such things. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
