mysql - Many null values in one table vs three tables -

i have 3 tables common fields - users, guests , admins.

the last 2 tables have of users fields. here's example:

users

id|username|password|email|city|country|phone|birthday|status 

guests

id|city|country|phone|birthday 

admins

id|username|password|status 

i'm wondering if it's better to:

a)use 1 table many null values

b)use 3 tables

the question less "one table many null versus 3 tables" data structure. real question how other tables in data structure refer these entities.

this classic situation, have "one-of" relationships , need represent them in sql. there "right" way, , have four tables:

• "users" (i can't think of name) encompass , have unique id referenced other tables
• "normal", "admins", "guests" each of have 1-0/1 relationship "users"

this allows other tables refer of 3 types of users, or users in general. important maintaining proper relationships.

you have suggested 2 shortcuts. 1 there no information "normal" users dispense table. however, means can't refer "normal" users in table.

often, when data structures similar, data denormalized single row (as in solution a).

all 3 approach reasonable, in context of applications have specific needs. performance, difference between having additional nullable columns minimal when data types variable length. if lot of additional columns numeric, these occupy real space when null, can factor in designing best solution.

in short, wouldn't choose between different options based on premature optimization of might better. choose between them based on overall data structure needed database, , in particular, relationships these entities have other entities.

edit:

then there question of id use specialized tables. there 2 ways of doing this. 1 have separate id, such adminid , guestid each of these tables. column in each table userid.

this makes sense when other entities have relationships these particular entities. instance, "admins" might have sub-system describes rights , roles , privileges have, perhaps along history of changes. these tables (ahem, entities) want refer adminid. and, should oblige letting them.

if don't have such tables, might still split out admins, because 100 integer columns need waste of space zillion other users. in case, can without separate id.

i want emphasize have asked question doesn't have "best" answer in general. have "correct" answer rules of normalization (that 4 tables 4 separate ids). best answer in given situation depends on overall data model.