Best way to build database??

[vonoventwin]

Trying to figure out what is the best way to set up this database before I really start diving in. I have about 900 people that I need to track training for. I need to record the date completed and then the next date due. My problem is all the training for each individual is the same, they all have the same 20 training tasks. So how do I set this up? I want to be able to see one persons record and it list all the 20 training for that person with the dates. Thanks in advance...

[wilsonsworld]

Hello

The simplest way is to hold it as one table:

Employee Task1 Task1Complete Task2 Task2Complete Task3 Task3Complete etc.

Hold your employees name in Employee (obviously), then in each task hold the pending date of training task, this will always be in the future. Replace each of these dates with the date that training was completed, which will always be in the past and flag the Task*Complete. A quick deduction in your code will gives you any relevant data you require including where training was missed.

[techgnome]

Blech....

Three tables:
Employee <- holds info about the employee, name, dept, etc. what ever you need to know about the employee.
Task <- holds the info on the task. NAme, description, start/end dates. This is about the task itself.
EmployeeTask <- holds the link between the employee and the task. Date assigned, date completed, emplID, TaskID, etc.

-tg

[sweet_dreams]

hi all,
wilsonsworld said:

The simplest way is to hold it as one table:

it is not advisable to do it using one table!!! You should never do it like that. I suggest you should read about creating databases especially about normalization.

The best way to solve this problem is to do it like techgnome suggested. It is standard (and the best solution for such a problem).

[AIS4U]

vonoventwin:

I'm not sure I would agree with SweetDreams statement "it is not advisable to do it using one table!!! You should never do it like that."

I am sure there are thousands of databases out there that only contain one table. In fact, one of the most common databases of all times is a Contacts Database that holds names, addresses, telephone numbers, etc., and is usually contained within one table.

As for normalization, you don't create additional tables containing unnecessary and worthless information just so you can normalize the database.

Also, everyone seems to be concerned with "Employees", but I can't find the word "employee" used in the original posting. How do you know the 900 people are employees? Perhaps they are students or volunteers or whatever, but there is not any indicatioin that they are employees.

All the original posting states is that "I need to record the date completed and then the next date due." And then that "all the training for each individual is the same, they all have the same 20 training tasks." So what is to relate?

The original posting just says "I want to be able to see one persons record and it list all the 20 training for that person with the dates.

It seems to me that a one table database would be quite sufficient for doing what the poster stated they wanted to do.

However, if there is a need for additional information such as the Trainee's name, address, etc., additional tables should probably be used.

I'm not a database expert. I just like to keep things as uncomplicated as possible.

Good Luck

[Dnereb]

Well... before anyone gets into a fight....
let's evaluate the data a bit more.

Before deciding on the amount of tables you have to look at the data itself.
- will there be reccuring data? like city's in the adress? or adresses (because they live in the same house/work for the same compagny/institution
- and will the number of courses evolve or be the same 20 in the comming X years

if you can answer these fundametals you can say something about the number of tables.

See Ya! Berend eeeh.... Dnereb

[techgnome]

Sigh... where to start? At the begining I guess.

vonoventwin:

I'm not sure I would agree with SweetDreams statement "it is not advisable to do it using one table!!! You should never do it like that."

I happen to agree with SweetDreams. But that's all opinion, and every one has theirs.

I am sure there are thousands of databases out there that only contain one table. In fact, one of the most common databases of all times is a Contacts Database that holds names, addresses, telephone numbers, etc., and is usually contained within one table.

Truse me when I say there isn't. Hundreds maybe, not thousands though. And trust me those are the most horribly worst databases to deal with. There's no flexibility and the data that you do get is more often than not crap. Stuff is needlessly repeated beyond comprehension. They don't scale well and God forbid should you need to add something to it (like track tow addresses instead of one.)

As for normalization, you don't create additional tables containing unnecessary and worthless information just so you can normalize the database.

I think you have that backwards. You don't needlessly create unnecessary and worthless information just so you don't normalize your data.

Example: Let's use the Employee situation here. Every employee is assigned to a department. Using your methods, there would be a Dept field, probably of string that contains the Dept name. Now comes the reorganization and a department gets renamed. What do you then? Open up each record and change it by hand? Run a SQL to do the update?

Now, let's "normalize" it, and change the field from Dept to DeptID and make it and int number. Create a new table called Departments. Two fields. DeptID and DeptName. Populate them accordingly and set the DeptID in the Employee tables. Now, when the Deptartment Name changes (And this does happen), all that needs to change is the DeptName in the Department table. THAT's the point of normalization.

Also, everyone seems to be concerned with "Employees", but I can't find the word "employee" used in the original posting. How do you know the 900 people are employees? Perhaps they are students or volunteers or whatever, but there is not any indicatioin that they are employees.

It was an assumption, not good to do, grnated, but harmless nonetheless. Could be tblPeople, or tblTrainees... what ever. The name is irellevant, but the concepts are sound.

All the original posting states is that "I need to record the date completed and then the next date due." And then that "all the training for each individual is the same, they all have the same 20 training tasks." So what is to relate?

Next week when another 20 training tasks need to be added. All that needs to be done is add a couple of data rows to a table and you're done. Other wise you have to open the table, add the fields, update any SQL statements to look at the new fields.... pain in the arse.

The original posting just says "I want to be able to see one persons record and it list all the 20 training for that person with the dates.
It seems to me that a one table database would be quite sufficient for doing what the poster stated they wanted to do.

So would the three table system:
Select *
FROM Employees E
INNER JOIN EmployeeTask ET
ON E.EmpID = ET.EmpID

done. Add an additional Where clause if you want.

However, if there is a need for additional information such as the Trainee's name, address, etc., additional tables should probably be used.

Now you contradict yourself. Why not throw that info into the one table? Otherwise you are "create[ing] additional tables containing unnecessary and worthless information".

I'm not a database expert. I just like to keep things as uncomplicated as possible.

Good Luck

This is going to come off as arrogant, but.... I AM an expert. This IS what I do. I have taken those tables where everything is all in one row and broken it down. Been there, done that. It's the dark side of the force. IT can and will make a mess of your data.

-tg

[Dnereb]

So the heat is already rising

To anyone listening... normalization is a means to an end not a goal in itself.
to answer the amount of tables you need to analyze the data and fore see the future (and opt for normalizatin if in doubt cause it doesn't hurt and keeps possibility's open)
and before somone hits me with a trout I and I'm an expert at that! (avatar)

[GaryMazzone]

I'm an Oracle DBA, when I start design of a database all tables are fully normalized. If a developer thens asks for de-normarilaztion of data I ask Wha will that buy the app? Will it cause issues with other developers? Are you going to all other developers and ask if the de-normalization of data will affect their portion of the app?

Normalize everything and let them fight to de-normalize.

[AIS4U]

Hey all:

Could someone please, please point out to me where the orginal post states anything about "Employees", "Dept.", or "adding 20 more items"?????

Trying to figure out what is the best way to set up this database before I really start diving in. I have about 900 people that I need to track training for. I need to record the date completed and then the next date due. My problem is all the training for each individual is the same, they all have the same 20 training tasks. So how do I set this up? I want to be able to see one persons record and it list all the 20 training for that person with the dates. Thanks in advance...

900 people, 20 training tasks, date completed, next date due.

Now I challenge all you database gurus to normalize that. Not by adding information that is not specifically stated, but just using the information given in the orginal posting.

There is a possibility that there might be some advantage to placing the 20 training tasks in a separate table and then relating them with a code number or such, but what else is there to do with the facts as they are stated????

Enjoy !

[szlamany]

Now I challenge all you database gurus to normalize that. Not by adding information that is not specifically stated, but just using the information given in the orginal posting.

This suggestion:

Employee Task1 Task1Complete Task2 Task2Complete Task3 Task3Complete etc.

Is simply not a reasonable suggestion. You cannot write queries against it - unless each and every employee has exactly the same task in TASK1 and TASK2 and so on.

Although the original post did state each employee has the same tasks - it also mentioned dates for next tasks and so on.

If all the tasks are in one row, finding a date that means something about the current task and the next task is not SQL friendly.

Now we actually do student applications - tracking 1000's of classes for 1000's of students every year - so we are already slanted toward a certain design.

I think that the bare minumum is.

EMPLOYEE TABLE - EMP ID and EMP NAME (and other demographic data as needed)
COURSE TABLE - CRS ID and COURSE NAME (and other specifics about COURSES).

CLASS TABLE - EMP ID+CRS ID make up the primary key - dates and other fields related to the course.

A CONTACT DATABASE is one table because all the info in a row is related to the CONTACT - CONTACTS have no relationship to other CONTACTS.

In my opinion taking normalization too far is taking CITY, STATE and ZIP out of an ADDRESS RECORD and only STORING the ZIP CODE. Then having a ZIP CODE table that supplies the CITY and STATE related to the ZIP. That's normalization "over-the-top".

[Dnereb]

AIS4U,
Wilsonworld inserted the employees in post two the first post speaks of persons only.
in fact it does not matter much if they are employees or volunteers or just a random bunnch of people.

Based only on the initial question you could make 1 table with the personsname lastname and postalcode a datedue field a tasktocompletfield and 20 booleans representing each task period.
but as this is advice........you could think about what it's for
one off the questions I raised is: will those 20 tasks stay the same tasks in the future.
These kind of things do matter for building a database design because you want to use it for more then 3 months or so (I hope).
but since vonoventwin hasn't respond to anything arguing about what is the best design in this case is pointless.

[DKenny]

I agree with Szlamany that this needs to be normalized. A table with a set of columns for each test, as proposed by wilsonsworld, is absolutly not the way to go.


Szlamany - One point of clarification: Your suggestion of

CLASS TABLE - EMP ID+CRS ID make up the primary key - dates and other fields related to the course.

is not consistent with the requirement to have multiple records of completion

I need to record the date completed and then the next date due.

If these are annual training requirements then the key is as you suggested with the addition of a YEAR field. (ie PK = EMP_ID+CRS_ID+YEAR_ID) .

[techgnome]

Just for the record... I wasn't trying to normalize the data in my example... it was simply the logical format for the data for the problem given. If I normalized it then 1) it was coinsidence and/or 2) I did it subconsiencously, which means it's second nature, and it probably the better way to go.

However, we've got a problem with all of the solutions given. We have 14 posts (incl this one, unless someone sneaks in) and only one of those is from the OP. So this discussion may all be for naught unless there's feed back from the OP. Personaly, I get irritated when OPs dont checkup on their threads and unless something truly compeling happens, I will be staying out until hearing from the OP.

=-tg

[salvelinus]

Hey all:

Could someone please, please point out to me where the orginal post states anything about "Employees", "Dept.", or "adding 20 more items"?????

Well, the title of the post is "Best way to build a database", not "Minimal amount of objects in database using only these parameters"

[szlamany]

It's ok to discuss this without the OP involved

We store marks for students - marking period 1, mp 2, mp 3 and mp 4.

We store all 4 of those marks in one record.

I've seen student admin packages that store each mark as a separate record - might get an A+ for normalization from your professor - but garbage for real life work.

I feel that the 4 marks a student gets every year in a class are "different in form" and can appear in one row - plus 25 years of coding against that type of data makes me happy with my choice.

There's my 2 cents - for what it's worth

[salvelinus]

What if they want to change the number of marks per year? I ask this because when I went to Michigan State, it was on a trimester system, same length overall as semester systems, but shorter terms. After I graduated, they went to a semester system more in line with other universities.
I don't know if they still do this, but I've heard in the UK some classes have one final, or paper, at the end of the year that's your whole grade. Ay yi yi! I've also heard of profs that give out a list of all possible exam questions at the start of the term, figuring if you can answer all those, you've learned what the class is about.

[szlamany]

Ok - I was just arguing both sides of the issue - since I do feel that course and date assigned and date completed do deserve to be in different rows.

Actually in our CLASS table we store 4 marking period marks - 2 exam marks, 1 semester average mark and a final mark.

Not all schools in a district need to use all these columns - actually only the high schools use the exam and semester average fields.

Some classes are half year - some just one marking period.

So the potential for these columns to be blank and meaningless is real - but still better then having 8 rows for each class for each student. We have all the control fields needed in other tables to indicate what mark "columns" are active for any given course.

The CLASS table at one of my larger customer sites has 600,000 rows right now. Making that a magnitude larger would be over-the-top in my opinion.

[rplcmint]

It's great to normalize tables since that is the way they teach you in the first place.....to make databases a science.

However, i've seen non-normalized tables in datawarehouse projects because it was easier to find data and make reports from them.

Diskspace is cheap, so do how you feel it is easier for you to extract the data.

[si_the_geek]

Data Warehouses are typically filled from normalised tables on a schedule (daily/hourly etc), as working with normalised tables is generally easier to fill/update the data - and the DW data is only used to generate reports.

I would not recommend avoiding normalising your tables based on disk space grounds, as the overhead of updating all rows of data when a partial update occurs will offset any advantage. However, if updates/inserts are very infrequent then a de-normalised data set is potentially a good solution.

[szlamany]

I was going to let this go, but since Si already chimed in, I'll add my 2-cents...

Data warehouses are designed flat intentionally - they do not follow normalization rules due to the method of reporting that is going to go against them.

They are not OLTP systems - which should be normalized - they are OLAP systems, which should be as flat as is required to allow the reporting to work. They do not get data updated into them - only data reporting is done (except for the initial periodic inserting of data).

The goal of an OLAP system is to allow users (low level) to peruse the data and get meaningful results.

Ok - maybe that was 3-cents...

[vonoventwin]

The OP is here! I have been reading over everything you guys have posted. First off, I think 3 tables will work best. The 900 people are employees of the Air National Guard branch in New York. Their training is the same (all 20 of them). I just need completed dates and due dates. So I'm thinking I will start building with three tables and see how it works out.