How to structure database tables?

Question

I am planning a database. It will track when a software program has been registered and log the information in the Registered table.

Two questions:

1: where should i log invalid registration attempts. For example if the user enters the wrong registration information or if they try to register but they have used all of their licenses. I want to remember this information but where do i put it?

I was thinking a separate FailiedRegiatration table or in general notifications table. What do you think?

2: Also if a user registers the same computer i want to allow them however i want to document that they reregistered the computer. Where should i store this information?

I was thinking making a DateRegiatered table that is linked to the Refistered table. That way for each successful registration i can keep track if someone reregisteres on the same computer.

Any comments are helpful as i think through this.

Thanks.

Answer 1

Hey,

1. If you need to specifically act on failed registrations, or later activate it and make it a successful registration table, store it in a separate table. If you only need to know about it, consider just storing the failure in a log table of some sort.

2. I think you want a separate table tracking user, and the machine registered on; that way, you know how many registrations a user performed, whether its 1, 2, or 10, etc. Just a pointer table that points to user ID and the registration...

My two cents.

Answer 2

Personally, I prefer to use logs, rather than database tables, to record "events" that are suitable for logging, and your "failed registration" event definitely seems to fall under this category (the "dates of registration" information is more debatable from this point of view).

Of course, that does depend on having a good logging system (with log rotation, etc) and a good log-processing system too -- many hosting providers, for example, may not give you those, though they'll typically let you use a relational DB.

If that's the case (you can't rely on "good logging and log processing", but rather whatever you do need to persist must go s/where in the DB), then one or more "log-like tables" (more or less like you outline) are a kind-of-OK workaround (and it's hard to suggest better ones, especially without enough info about your deployment situation;-).

Answer 3

I think 2 tables would work. One table to track users (eg: id, username, serial, email), and one table to track registrations (id, foreign key to the users table, timestamp, record of success or failure, and some field to id the user's computer).

The second table would be your log table and have entries for successful initial reg, successful re-registration, and failed registration attempts. no?

Depending on how much information on the user's machine you have you can come up with various ways to ID if it is the same machine or not. This is a hard problem though.