I'm working on the conceptual phase of modeling a database for a company right now.

I have two considerations:

1) This database will obviously be integrated into a web application, but more importantly it will be a user/pass based system with rights and privileges.

2) I trying to model a scheduling system for a particular business where users can login and register for appointments to have certain things done for them.


I have thought of a couple different variations but I've found many of them to hop from one side of the fence to the other (in terms of difficulty). By this I mean that either one approach will make the coding simpler ... but will make the tables more non-intrinsic and possibly not meeting 4th Normal Form OR the model will make coding a nightmare.

Here is my most recent model:

User
-------
id -> key
pass
privileges

Client
--------
userID -> key
Appointments


ServiceProvider
------------------
userID -> key
Appointments
Services (offered)
Clients

Appointment
---------------
id -> key
time
date
Services (performed)

Services
-----------
id -> key
name

Personal_Info
----------------
userID -> key
fName
lName
Phone Numbers

Phone_Number
-----------------
[composite key]
userID -> key
number -> key


_________________________

Like I said, I'm not even worried about bridge tables or the handling of 1:Many relationships, Just Drafting the Model.


For some reason this design feels "iffy" at best. Something about making the User table have a direct relationship to every table feels wrong ... but if not wrong then dangerous ... because then there exists a dependency for any entry from any table to exist such that there is a corresponding User entry to facilitate it.

Any thoughts, comments, or suggestions?

Thanks,

Matthew Cox

Oh, and side note. There is one other thing to consider.

There exists a heirachy within Service_Providers

Owner
Worker

I opted not to use two seperate tables because this would increase programming complexity. ... I would have to implement to determine, in some fashion, wether the person logging in was a owner or a worker ... in that case, I would have If statements all over the place everytime I wanted to do a query. Because I won't intutively know what type of user the person is so therefore, I wouldn't know automatically which table to query.
Not to mention the fact that Owner and Service Provider have almost identical table attributes... in programming inheritence would solve this problem easily ... but I know no way of doing that in DB Design

Here's a few questions in an attempt to clarify your problem:

1) Can a given User also be a Client and a Service Provider at certain times?
2) Inversely, are Service providers and Clients always Users of the system?
3) Was there a reason to put the Personal Info in a separate table from User?
4) Can a User have more than one phone number?

Here's what I see your model might be, before I get an answer to the questions:

User
-------
id -> key
fName
lName
pass
privileges
service_provider_class (owner or worker or NULL if this user cannot be a service provider)
client_class (normal or NULL if this user cannot be a client)

Appointment
---------------
id -> key
Provider_UserID (foreign key: that's the User who acts as the Service Provider)
Client_UserID (foreign key: that's the User who acts as the Client)
time
date

Services
-----------
id -> key
AppointmentID (foreign key)
name

Phone_Number
-----------------
id -> key
userID (foreign key)
number