October 10, 2002 at 5:03 am
Hi
I'd like some advice on how to go about designing this database.
I'm planning on having 4 tables Person, Date, Task, Timespent.
In Person there will be PersonID, First_name, Last_name, Department.
The other 3 tables have only the primary key in them. ie. Date(table) Date(PK) etc.
Is it wise to add PersonID as a foreign key to each of the other 3 tables (date, Task, Timespent)?
Can you offer advise on a better way of doing it?
Also, The Task field in the Task table will have free text ie. finished early, out to lunch etc. So it isn't just 1 value in the column, what's the best data type for this? Will varchar do it?
Any advice is appreciated
October 10, 2002 at 8:19 am
First, if you include the PersonID in the other three tables it will make JOINs easier. For example:
Select *
From Person P
Left JOIN Date D
On P.PersonID = D.PersonId
Last, varchar will do it and will probably be the best. If you use CHAR you have to estimate as accurately as possible how many characters will be in the column and all possible entries should be close to the same amount of characters. With CHAR all 'spaces' are used, so CHAR(10) means that ten spaces in the column will be used whether there is data in them or not. VARCHAR is 'adjustable' in that it only uses what it needs. So VARCHAR(10) doesn't waste space if your entry isn't 10 characters.
Check this out in the Books Online for more indepth explaination.
-SQLBill
October 10, 2002 at 8:49 am
This is just my opinion, but wouldn't Person and Task be your entities, and TimeSpent and Date be descriptors of these entities.....
What would be the objective of creating Date and TimeSpent as Entities? Would these tables simply standardize lookup values or something? Would this facilitate OLAP functions you are preparing for?
Would not Person to Task be a one to many relationship, with the date of the task being a field, and the time spent on this task be another field?
Also, Would a single task be able to be worked on by multiple people, thereby requiring a relationship which would change the above relationship to a many to many possibly?
Another thought would be about groupings. Is it possible to re-assign a task, yet keep ownership of the task on the origional owner, thereby creating the need for multiple levels of taks to person assignment?
I simply submit these ideas as ideas to think about, but what business requirements are on these entities?
Edited by - Scorpion_66 on 10/10/2002 08:55:11 AM
October 10, 2002 at 9:52 am
Hi
Scorpian_66: What you say makes sense and I've changed my model now to the 2 tables, Person -> Task.
I'm trying to think about the end result which will be a VB front end so that engineers in the field can log on and update there timesheets straight into the database. It will be one engineer doing multiple tasks and they can't be re-assigned.
As for the front end I'm looking at having the following fields
Person
Date
Task
Timespent
Department
With that in mind if I use PersonID for the Primary Key (and Foreign Key) the engineer will have to input his ID right? So will I have to make a field on the VB front end for ID?
Sorry for the (dumb?) questions but this is my first project and I'm just trying to get my head round it! 🙂
Thanks for the help both of you.
October 10, 2002 at 10:08 am
I have never believed there was a such thing as a dumb question, though I get flamed on a regular basis for what people tell me is a dumb question. If the question is asked, its because someone needs to know something, and though some people may think its dumb, to the person asking its important. I believe that its smarter to ask about things you don't know, generate some conversation and get all the perspectives you can on it. A lot of times, what is assumed by some to be obvious, is wrong, and could have been avoided by asking a "dumb" question. etc. etc. etc.
OK, off the soap box.
Anyway, I believe that if the person is logging in, there personid would be better retrieved from the db for integrity sake if nothing else. Make the person type as little as possible, and your data will stay more consistent. (type-o's, etc...) It would also be pre-validated that way, and the person would never have to even see or remember their ID.
Again, just my opinion....
October 11, 2002 at 2:04 am
Hi
So I'd pre generate the numbers for the different engineers and then use a trigger to insert the ID numbers when 'Bob' filled in a form?
Thanks for the advice, your really helping me out!
October 11, 2002 at 8:18 am
The scenario as I think about it would be closer to:
The person table would be populated with all your person data from the start.
The Data access page would get the person data from the person table and hold it in variables while the person added his task data. The page would take the data entered, validate it by your business rules, and insert a row in tasks. No triggers. Just a simple insert of task data. In fact, I believe that 3 procs would do it.
1 to get person data based on the log-in.
1 to get task data to show on the page, so the person could review what tasks he has entered, maybe broke down by day.
and finally,
1 to enter or update new task data.
A very simple and straight forward approach....
After all, the fewer moving parts, the better.
If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....
October 11, 2002 at 8:31 am
Another approach, would be to use a single proc, which grabbed the client data and task data as a recordset, with one switch, and updated / inserted with another. However, this approach would be higher in cost as every page refresh would hit the person data unneccessarily, it complicates the proc itself, and includes joins which are not really needed, in my opinion.
I throw this out there, simply to give an alternate, that may or may not fit your business needs better. Though it overall costs more in resources, this would allow for more secure data access, no need to worry about variables to hold data in the page, possiblity of using a composite view for data entry and update, rather than two tables, etc....
(Anyone else could jump in here, and give Alton more perspectives, at any time.......grin)
If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....
October 11, 2002 at 8:47 am
That's great Scorpion_66, it's much clearer now.
The thing that is confusing me is where the SQL stops and the VB begins. I'm going to read up on VB some more and try a few small projects just to get a feel for it.
You've been a great help! 🙂
October 11, 2002 at 9:55 am
Now thats an age old debate.....
Where should VB stop and SQL start?
Do some research on application design models. Over the years, the opinion of whats "Best" has changed dramatically.
There has been many many models, including but certianly not limited to:
Client - Server variations - Where the VB client interacts directly with the Sql Server, with the business logic was either in the VB client, or in the Sql Procs, or a combination of both...
Nth-Tier Model variations - Where the vb app interacts with components which interact with the Server, and again the business logic is in one of the three, or a combination or them, etc...
I believe that each has its merits and each also has its downside, and an evaluation of what fits best per situation is the proper way to go. Its all a balancing act in my mind.
Just always keep in mind that what is designed today, will most likely be modified tomorrow. Unless you have a very good project manager, count on Scope Creep, and for heaven's sake, document everything in detail. If I had a dollar for every time I heard the line "All I want is....." , I would retire today.
I sure wish someone else would give some opinions, and some different perspectives, and certianly, do not take what I say as gospel, but as considerations to be researched, and applied if found applicable.
If your not gonna help, Please move to the side, because if your not helping the situation, your hurting it....
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply