MySQL table schema help needed

Question

I've a problem deciding where to place a certain table field within my database. The database is for a classified ads website. I want registered and non-registered users to be able to post ads. If an unregistered user posts an ad, the site should ask him for the contact phone, if the user is already registered, then the contact phone stored in the users' table should be used.

Now my question is, would it be possible to store the contact phone for both registered and unregistered users in the same table field? If so, where should that field be put, in the Classified ads table, or in the users' table (noting that each user within the table has a unique Id, thus, filling the users' table with unregistered users just to get their contact phone will just fill the table with useless data)

Thanks in advance !

Answer 1

You can store unregistered users' phone numbers in the same column of the same table, and you probably should. It makes the transition from unregistered user to registered user dead simple--you don't have to move or re-enter phone numbers. And if any user changes phone numbers, it only has to be updated in one place. (Do you know how many people accidentally drop their cell phones in a toilet every day? It's staggering.)

If you're now relying on the presence of a phone number to identify registered users, you'll need to fix that first. (I don't think you're doing that, but if you are, fix that first.)

You said

filling the users' table with unregistered users just to get their contact phone will just fill the table with useless data

If it's useless, don't store it. If you need to store it, it's not useless.

You can always delete unregistered users when their classified ad terminates. But . . .

Does it make sense to require a contact phone number instead letting the user choose to leave either a phone number or an email address? Personally, I prefer to use a throw-away email address for things like this.

Answer 2

I would look for flexibility in your design, but if your logic treats users mostly independently of whether they are registered or not, I would use the same table and just complete the rest of the user's data for the registered ones. I wouldn't store user data on the ads table, even if only for clarity of data organization.

I guess your registered users will all have a username and password, so you can just check the presence of these to know if they are registered or not. If you don't want to change your logic in the future you should choose this distinction carefully of course.

A different approach: Why not making registration so easy that it makes no sense to have unregistered users? I you are already asking for a phone number in all cases, just add a password field or generate one automatically and you have all your users registered.

I would ask for an email so you can send the password and perhaps ask for account verification (now or in the future).

Answer 3

well you can put the phone field in the ads table, with a is_registered field inside. Then via php you check is_registered and then you know where to search for phone number.

Regards