basic databases design question

Nulty

Hey folks maybe you can help me with this. I'm making a carpool application for a large organisation (not real).

I have 3 tables:

Users

Locations

Arrivetimes

I'm not sure I need the arrivetimes table though. The arrivetimes has id,mon,tue,wed,thu,fri,user_id as field names. Each record belongs to 1 user only . I update that table through the user update application page and therefore don't need an arrive times update page or any other.

Is it better for arrivetimes to have itsown table or should the arrivetimes appear in the user table under the same fieldnames.

Thanks

Nulty

I should say that I create the available arrive times (07.00, 07.30, 08.00, etc) on the user create and update pages so I can't change the available times to enter into the database. I mean I can't add 07.15 and 07.45 to the application if I wanted to in the future which doesn't seem like a good idea. The user chooses the arrive times from a select menu on the html page. and that is populated by a PHP function on the page.

Tom Dunne

When talking database design, forget about tables and columns and PHP for the moment, and describe verbally what you want.

Then we can talk about entities and relationships Only then can we talk tables.

(I assume this is college work?)

Nulty

Thanks Tom, yes its a college project.

I'll try explain it as you asked.

Users will sign up to the site and will enter into their profile times of the day that they arrive at the college. They will say they arrive at 10am on Monday, they will say they don't go to college on Tuesdays and arrive at college at 1PM on Wednesdays, etc. They will also define their location as per selecting a Dublin post code, i.e., Dublin 1, Dublin 2 etc.

The idea is that users will search for other users who arrive at college at the same time as they do from the same area as they live so they can arrange a carpool with them.

Is that any better?

Tom Dunne

Nulty wrote: »

Is that any better?

Much better.

Now, forget about user logins/signups and all that stuff and determine what exactly you are storing in the DB.

You mention users and you mention profiles - are they the same thing? What exactly are they? As in, what will be in a user/profile or user profile?

Also, locations, how are you going to handle them - free text (shudder!) or a list?

Finally, how will you record the "Go to college/don't go to college"?

Nulty

Users and profiles are interchangeable I guess. User is the person controlling the profile and profile is the data associated with the user, a representation of the users attributes.

User/profile is an email, student number, password, location, arrive time for each day of the week(Mon - Fri), Administrator flag etc.

Locations are handled with a set list of Dublin post codes 1 through 24.

Again, the times are handled with a list starting with "none" for no arrive time indicating that the user will not go to college that day. It is an item (default) on the list.

Tom Dunne

Nulty wrote: »

Users and profiles are interchangeable I guess. User is the person controlling the profile and profile is the data associated with the user, a representation of the users attributes.

Ok, so the first thing I would do here is determine on a name - is it going to be user or profile?

Nulty wrote: »

User/profile is an email, student number, password, location, arrive time for each day of the week(Mon - Fri), Administrator flag etc.

Right, do you see different "types" or "groups" of data there? To put it another way, do you see data that could be grouped under different classifications? (you know where I am going with this, right? )

Nulty wrote: »

Locations are handled with a set list of Dublin post codes 1 through 24.

That seems fairly straight forward.

Nulty wrote: »

Again, the times are handled with a list starting with "none" for no arrive time indicating that the user will not go to college that day. It is an item (default) on the list.

Now, this is an interesting one. Do you need to record data where there is no data to record?

Nulty

Oh, I've settled on user as the name. I forgot to mention I'm recording the persons first and last name for user seems more suitable.

The way I see it is that location is a type of data and arrive times are another type of data. The rest are likely to be unique to the user where as those two are from a limited range of data.

I suppose there is no need to record the absence of data. I could leave that as null. When the users search the application for people the information they receive is a full set of arrive times for the query. Something has to be displayed under the heading of Monday even if that person doesn't go to college on Monday. But does that need to be in explicitly recorded? I don't know.

DublinWriter

Nulty wrote: »

The arrivetimes has id,mon,tue,wed,thu,fri,user_id as field names. Each record belongs to 1 user only

Probably easier/smarter just to have four fields here:

1. id
2. dow (Day of week)
3. arrivetime
4. user_id

That should make your searching queries a little neater as you won't have to hard-code in days of the week into your queries to match people with the same specific daily start times.

But what about flexi-time workers that can clock-in within a two-hour window?!?!? (sorry, too much coffee).

DublinWriter

Nulty wrote: »

Oh, I've settled on user as the name. I forgot to mention I'm recording the persons first and last name for user seems more suitable.

Let's hope there will never be two John Smiths working there!

Tom Dunne

Nulty wrote: »

Oh, I've settled on user as the name. I forgot to mention I'm recording the persons first and last name for user seems more suitable.

That's fine so. Remember that for later.

Nulty wrote: »

The way I see it is that location is a type of data and arrive times are another type of data.

Right, so at this stage are they types of data or are they entities?

Nulty wrote: »

I suppose there is no need to record the absence of data. I could leave that as null.

Leave it as null, or simply not record it?

Nulty wrote: »

When the users search the application for people the information they receive is a full set of arrive times for the query.

I am not following you on this one.

Nulty wrote: »

Something has to be displayed under the heading of Monday even if that person doesn't go to college on Monday. But does that need to be in explicitly recorded? I don't know.

Jumping ahead a bit, if the SQL query returns noting for a day, at what layer are you going to handle what is displayed? As in, do you store "no college this day" in the database, or do you let the calling code determine "IF SQLQUERY IS NULL THEN DISPLAY TEXT= 'don't go to college this day' " or something along those lines.

Nulty

Tom Dunne wrote: »

That's fine so. Remember that for later.

Ok

Tom Dunne wrote: »

Right, so at this stage are they types of data or are they entities?

Entities I think. I'm not overly familiar with the terminology. Oh, wait, data types would be ints, dates etc. So those are entities.

Tom Dunne wrote: »

Leave it as null, or simply not record it?

You mean instead of the value defaulting to null it would be empty?

Tom Dunne wrote: »

I am not following you on this one.

Probably my misuse of formal terminology.

User searches the app for people in D18 who arrive on mondays before 12PM. This would fill a table with the results matching the query. In that table would be the peoples names, location and their arrive times for each day of the week. I think you answered my question below though. Create the display value for "No College on Monday" with the calling code rather than with data direct from the database.

Tom Dunne wrote: »

Jumping ahead a bit, if the SQL query returns noting for a day, at what layer are you going to handle what is displayed? As in, do you store "no college this day" in the database, or do you let the calling code determine "IF SQLQUERY IS NULL THEN DISPLAY TEXT= 'don't go to college this day' " or something along those lines.

Gotcha

Nulty

So, can anyone help me with my problem? Should the arrive times be in a separate table or in the user table. My gut feeling is that they should be in the user table.

croo

While some people, usually with a lot of experience, might design databases on gut feelings, I believe you can follow very specific & relatively simple rules to get to a good design; namely the database normalization rules. http://en.wikipedia.org/wiki/Database_normalization

While some of the later normalized forms can be a little complex, I think if you normalize your db to just the 3rd normal form that usually is sufficient.
This page http://www.phlonx.com/resources/nf3/ provides what I think is a good and, perhaps more importantly, simple example of the kind of process you should follow to determine if your design is in the 3rd.

Nulty

DublinWriter wrote: »

Probably easier/smarter just to have four fields here:

1. id
2. dow (Day of week)
3. arrivetime
4. user_id

That should make your searching queries a little neater as you won't have to hard-code in days of the week into your queries to match people with the same specific daily start times.

But what about flexi-time workers that can clock-in within a two-hour window?!?!? (sorry, too much coffee).

I didn't see your posts until now.

I don't really see how that will work. As for the flexi-time thing...you've totally lost me.

DublinWriter wrote: »

Let's hope there will never be two John Smiths working there!

What bearing would that have? You mean a unique public identifier would be better like a student number?

Adam

I'm not expert on database design, but this seems fairly simple to me. I'd go with 2 tables, users and days(maybe not the most descriptive name)

users contains user_id, name, student_number, email etc. anything unique to each person

days has day and time

user signs up, adds their personal info and enters their location and arrival times for monday through friday. each entry goes in to days as follows:

id     day     arriv
-----------------
1     Mon     09:00
2     Tue     11:00
3     Thur    13:00
4     Wed    10:00

etc.

This way, when a user wants to do a search for people that arrive on monday at 12, they select the day and time via select boxes and your sql queries days for all entries that are on monday at 12. and you could develop a nice algorithm to check for times that are close to one another so that if someone entered that they arrive in college at 11:15 or 12:45 your program will return these.

or maybe i've forgotten something or that's terrible database design! :P

edit: yes i did miss something, the location of the user. i guess you could add that to the days table as well, or add a user_id element to the table to use to query the location from the users table...maybe i've lost the run of it now!

DublinWriter

Nulty wrote: »

So, can anyone help me with my problem? Should the arrive times be in a separate table or in the user table. My gut feeling is that they should be in the user table.

I would think arrival times should be in the main times table for the purpose of your exercise.

For extra brownie points you might want to have two arrival time fields - timemin and timemax as quite a few people work flexi-time these days - eg. John Smith might get in between 8am and 10am, but Paul Brown needs to get in by 9am, so John could theoretically share with Paul.

The username should be unique, but it also shouldn't be your primary key in this table if you follow my table design as I'm suggesting not to hard code in the days. Your PK in the table should be an auto-incrementing numeric.

To backtrack, you'll have the following tables:

TBL_USERS (pk is the student_id)

Fields:
student_id (Primary key)
surname
firstname

TBL_ARRIVAL_TIMES (pk is auto incrementing numeric, FK is student_id)

Fields:
id (pk, auto incrementing numeric)
student_id (foreign key)
day_of_week (either one of Mon, Tue, Wed, Thurs, Fri, Sat, Sun)
arrive_time_min
arrive_time_max

TBL_USERS would be related to TBL_ARRIVAL TIMES one-to-many


Clear enough?

Just out of curiosity, does your course cover Third Normal Form database design?

Nulty

DublinWriter wrote: »

I would think arrival times should be in the main times table for the purpose of your exercise.

I thought so.

DublinWriter wrote: »

For extra brownie points you might want to have two arrival time fields - timemin and timemax as quite a few people work flexi-time these days - eg. John Smith might get in between 8am and 10am, but Paul Brown needs to get in by 9am, so John could theoretically share with Paul.

Good idea but I'm handing this in on Friday as a soft copy.

DublinWriter wrote: »

The username should be unique, but it also shouldn't be your primary key in this table if you follow my table design as I'm suggesting not to hard code in the days. Your PK in the table should be an auto-incrementing numeric.

In theory I agree, I can use the student number instead of a username as a unique identifier and leave the real names there.

DublinWriter wrote: »

To backtrack, you'll have the following tables:

TBL_USERS (pk is the student_id)
TBL_ARRIVAL_TIMES (pk is auto incrementing numeric, FK is student_id)

Thats what I've done. Theres a Locations table too though.

DublinWriter wrote: »

Clear enough?

Crystal. Why didn't you just say that in the first place?

DublinWriter wrote: »

Just out of curiosity, does your course cover Third Normal Form database design?

Yes, they do that in the Introduction to Databases and Information Systems Module. It starts next week.

Thanks for taking the time DW.

DublinWriter

Nulty wrote: »

Yes, they do that in the Introduction to Databases and Information Systems Module. It starts next week.

Thanks for taking the time DW.

No problem.

Personally, I'd be wondering why they gave you an exercise like this before covering Database design and 3NF?

Nulty

DublinWriter wrote: »

No problem.

Personally, I'd be wondering why they gave you an exercise like this before covering Database design and 3NF?

TBH, I think I'm paying too much attention to the database design for this project. Its a web technologies module and I should be focussing more on accessibility, usability and other such interesting topics. My database is just bugging me and I want to find the best way to do it.

DublinWriter

Nulty wrote: »

TBH, I think I'm paying too much attention to the database design for this project. Its a web technologies module and I should be focussing more on accessibility, usability and other such interesting topics. My database is just bugging me and I want to find the best way to do it.

Sure. Good DB design is the backbone of any system, glad to see you've got your priorities right! UI's can always be changed later on.