Database Design OutSystems

Database Design OutSystems

  

I've done the Tutorial and It has opened my eyes to the capability of OutSystems, but I am still having a hard grasping DatabaseDesign using the OutSystem. I dont know where this mental block is coming from right now. I am hoping someone can help dislodge it and get my thinking pushed in the right direction.

I am creating a Database that will store all employees and employee training.


Employees

-FullName

-AodNum

-PayClass

-Agency

-HireDate

-Department

-Shift


Example Database 

- Training Process1

- Training Process2

- Training Process3

- Training Process4

- Training Process1Score

- Training Process2Score

- Training Process3Score

- TrainingProcess1TargetCompletetionDate

-TrainingProcess2TargetCompletetionDate


So I need the Training Process to Link back to that employee from the employee database and store the processes he has completed, the process he is working on (if any), ProcessScore, and the predicted completion date entered by the trainer.


Can someone just push me in the right direction in linking these databases and if two databases is enough to store this data? Just looking for a nudge if someone can help me out. 

Solution

Hello Jared, welcome to the community,

How much do you know about Database modelling?
Maybe you should invest sometime in studying this before. It will be worth.

You probably is better with something like this:

Emplyee (id, FullName, AodNum, ...)
Training (id, TrainingDescription, ...)
TrainingEmployee (id, EmployeeId, TrainingId, ComplationDate, Score, ...)

This way, if an emplyee can do many different trainnings, and a trainning can be done by many different employees, you have a M..N relationship (many to many) and this way you need a third table to connect employees and trainings.

Hope this helps.

Cheers 

Solution

Actually i realized i have never studied Database Design / Modeling, just SQL / Queries. 

I just ordered

Database Modeling for Mere Mortals.

---------


Is there any chance you would finish out the information you would put in the three tables to let me practice while that book is coming in and if you were going to put in a Boolean (Check mark saying they are qualified on a vehicle) which table would you put that in? Again, I am sorry to ask this, but I am very interested in playing around with the initial steps of the database! I hope I dont agitate you by asking this! 

This was the last question regarding this issue, you've given me food for thought if you dont have time to continue.



Hello Jared,

While I can help, there is no problem.
Think the following.

If "a vehicle" is something that the user is qualified or not, and this is very generic, there are no types of vehicles and the training is not related to this, so, you could put this information in the "employee" entity. Even if I think in the future, with more information, you would probably put this information in an extension entity (grouping related information).

But if the vehicle is something that depends on the training, than maybe you don't even need this flag? Maybe the fact he finished certain training gives him automatically the possibility to use a vehicle?

Or, again, if the vehicle is not associated with the training, but he can be associated with a "specific" vehicle, you could have a third entity Vehicle(id, vehicle, plate, ...), and an association VehicleEmployee(Id, VehicleID, EmployeeId)...

And of course, there are other possibilities.

Everything depends a lot on the type of information and a series of constraints you are not making explicit here :)   

When you typed this out

TrainingEmployee (id, EmployeeId, TrainingId, ComplationDate, Score, ...)

Was this a database you imagine only storing employees under going active training or this database I copy and pasted above meant to store employees who have completed training as well? 

I would say that both finished and ongoing training. The same employee could even figure more than once here... 

Thank you, Eduardo! Getting that settled one last question.


I have decided to split employee names up into separate entities. First, Middle, Last 

How do I set a training process to match that employee's full name when their First, Middle, and Last name are in separate columns?

Hi Jared,

I'm not quite sure what you mean (but perhaps Eduardo does :)). You want to match a full name against the seperate columns? So e.g. "Jared Davis" against "Jared", "", "Davis"?

Hi,

You mean that you separate the separate columns, not entities. :)
Doesn't matter, because to match records in different tables, you will always (or almost always) use the identifiers and foreign keys. 

So, if you find an employee in Employee table, searching for the First Name + Middle Name + Last Name, you know its ID, and than you can use it to find the Training, or even joing those two tables using the Id and Foreign key.

Cheers.

I guess I should get the fundamentals before I asked the question above

EmpInfo Entitiy

- FirstName

- MiddleName

- LastName

-AodNum

-PayClass

-Agency

-HireDate

-Department

-Shift


------------------------------

How would I ensure the person's First,Middle,Last name correlate every time someone pulls information from the table in OutSystems? For example if they want to look up Jarod Michael Davis how do i make this individual even if someone has the excact same name? 


Again, I have a book coming to help me with all this, but just trying to setup EmpInfo table and play with it until the book gets here Friday. Thank you so much for your responses.

--------------------------------

I think I am an idiot. Each row has an ID and each column inputs that user's information on that row.

So First,Middle,Last,AOD,Pay class is all on one row so all this information for this person is linked to him by the ID of the row, right?


Maybe I need to make some kind of Composite Key to link First,Middle,Last to a single ID?


Yes, it's a single row, you'd only use the Id for identification and linking.

As for the composite key, you can do that, but you only need that if you will often search on all of these attributes at the same time, otherwise it's a waste of database space and time (when inserting a new record, keys take time to create).