I am focusing on a web-based system that enables customers to have interaction socially not to mention it will likely be vital that you have the ability to identify customers that are actually online. I understand about HTTP being stateless and that i learn about using periods, so I'll need to do this by evaluating the final active duration of a person for an arbitrary expiration time.
My ultimate question comes lower for this : Must I just then add fields towards the existing people table (
hide_online_status, etc) OR will it be better to maintain these details inside a separate table? My primary thought is by using the present table for simplicity. Aside for degree of complexity, do you know the benefits/disadvantages of 1 versus another?
Produce a new table.
First, there is the philosophical reason, that is that certain object (read: table) must have one purpose. That will get blurred a great deal in database logical design, but it is a good principle nevertheless.
However the real reasons come from physical design: first, the amount of customers that'll be drenched on at any time could be assumed to become far under the entire quantity of customers, to ensure that storage needs is going to be lower. Minimizing storage needs translate straight into reduced strain on the database, due to the fact you've less blocks to see increase.
Should you put this data inside your primary table, furthermore, than individuals blocks is going to be scattered on the much wider space, and also the items in the blocks will constantly grow and shrink while you alter the data.
And lastly, you'll most likely require some indexes, for instance to determine who's online now. And individuals indexes will (1) occupy much more space, (2) create much more scattered dirty blocks that has to be physically written to disk, and (3) introduce a place of update contention.
I'd maintain this inside a separate table. For those who have possess a million customers and you'd like to learn who's online, you don't want to become checking that table again and again again to locate that information. Your "online" table is going to be relatively small , you'll have a job that scans it periodically for individuals that haven't are available in previously 5 min approximately after which simply remove them in the online table increase anything necessary within the people table for "last_seen"
It is dependent on the number of customers you have a much. If you will find only likely to be a few 1000 customers, I'd simply make the internet status a characteristic of the user, within the user table. If you will find likely to be in addition to that, then you will want a web-based_customers table, and that information there, having a foreign answer to the primary customers table. To reap the advantages (pointed out by above posters), you will want an activity to operate every couple of hrs approximately to get rid of inactive customers in the online_customers table.