3

I have a user table that controls access to a website. We currently have a few hundred users and this might eventually grow to a few 10,000s. We have a requirement to allow in "temporary" users. These temporary users will have a token that times out, never to be used again. These tokens will likely outnumber the general users by a great degree. The question I have is whether these temporary users should be stored in the general table or in their own table.

My inclination is the same table as the userid is used elsewhere as foreign keys and is still useful for a temporary user. The uniqueness across the ids would be important. However, I'm not delighted that the user table will be filled with many records that never again need to be used and will thus slow down the table.

Another option I've considered is to create a user record, capture the id, delete the record and then use the id in another table. I therefore retain the uniqueness of the id but reduce the bloating of the table. I don't mind if the foreign keys reference different tables.

Anyone had a similar issue and have any thoughts?

1
  • What database solution are you using?
    – Ryan
    Dec 8, 2011 at 17:26

3 Answers 3

5

Why don't you just store it in the main user table (keeping a unique ID will be a nightmare otherwise), and have an ACTIVE bit field that you include in your indexes (you can filter if you like).

It won't really slow down access to the table since your indexes will be able to very quickly sort by active/inactive but you also avoid the headaches caused by trying to "roll your own" identity field.

2
  • Thanks, I've now gone with this. I really just needed reassurance I wasn't missing something as it feels a little odd filling the table with records that are to be used quite differently, although logically they are the same. However, my discomfort wasn't matched by any real technical concerns or objections.
    – Raspin
    Dec 9, 2011 at 11:22
  • As long as the records represent the same kind of objects (in this case users), it should be fine.
    – JNK
    Dec 9, 2011 at 13:08
2

Put an expiry date into your user table, then every week shift all old expired users to an archive table. You don't have to break your data model for temporary users, and you keep a reasonably small users table.

1

A user is a user, temporary or not. As JNK said previously, indexes will help prevent the performance slow downs, as long as you queries are making use of the properly indexed columns.

If the user's are truly temporary, you can have a cleanup script that would delete/archive expired user accounts and related data.

1
  • I think the true "temporariness" is a key issue here. I say temporary, in that they will be single use (or uses over a short timeframe) but given I create audit trails all over the place they are not truly temporary. That was a another reason in the end to go with the general users table. Once the system is in test or even in production I'll monitor usage and performance and if there is any possibility to clean up I will do so.
    – Raspin
    Dec 9, 2011 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.