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).

Hi Jared,


We had the same issues, do you manage to complete this database?



Eduardo Jauch wrote:

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 

Hi Eduardo,

I Just want to verify if the table of Employee & Training are entities, and the TrainingEmployee should be Static Entity?


Thank you.


Hi,

If it was static, you would not be able to connect to normal entities, as static entities can have FK for normal entities, and you can't add rows to them in run time.

Cheers

Eduardo Jauch wrote:

Hi,

If it was static, you would not be able to connect to normal entities, as static entities can have FK for normal entities, and you can't add rows to them in run time.

Cheers

This is noted Eduardo, 

I have the same project like jared, and I'm new user in OutSystem. 

Is there any tutorials for creating database storing all the training's attended? 


Database design is not something specific for OutSystems. 

I recommend you doing a database course or reading and studying a database design introductory course. You may find a suitable book or online training on this subject. Don't have a suggestion, unfortunately.

As per curiosity, are you doing a training of some sort, that requires you to do this? If that is the case, I suggest you to talk with your trainer about extra resources on this subject. He/she may have suggestions of links and books.

Cheers

Eduardo Jauch wrote:

Database design is not something specific for OutSystems. 

I recommend you doing a database course or reading and studying a database design introductory course. You may find a suitable book or online training on this subject. Don't have a suggestion, unfortunately.

As per curiosity, are you doing a training of some sort, that requires you to do this? If that is the case, I suggest you to talk with your trainer about extra resources on this subject. He/she may have suggestions of links and books.

Cheers

No Sir,

This my project in my department, It's quite hard for me to create an Employee Training Record Database. That's why i'm asking. 

Thank you, sir. 

Oliver Tandayu wrote:

This my project in my department, It's quite hard for me to create an Employee Training Record Database. 

Hi Oliver, I don't quite understand: how can you have the exact same project in your department as someone else not in your department (or company) had almost a year ago?

Kilian Hekhuis wrote:

Oliver Tandayu wrote:

This my project in my department, It's quite hard for me to create an Employee Training Record Database. 

Hi Oliver, I don't quite understand: how can you have the exact same project in your department as someone else not in your department (or company) had almost a year ago?

Sir Killian,

Were trying to consolidate/unify all training records of the employee. Because per training there are persons who handle it. For example Internal Training care of Employee one, External Training care of Employee two, Non Technical Training care of Employee three.  Sometimes we had a Data redundancy and we don't have consolidate training for our employee.


And I'm the one in charge to create that Consolidate Employee training records sir. I'm newbie in the OutSystems.


Thank you.




Oliver,

Being newbie in OutSystems is not a problem. You have the online trainings to learn how to program in OutSystems, and we can help you with concepts you may be struggling.

But database design is a skill by itself. If you are not skilled in database design, you will have to study it.

Otherwise, we would have to guide you through the entire process of your requirement, and this would meant we would be doing your job (without being paid for that), right?

So, if you have specific questions about how to implement a specific design in OutSystems, or you have an idea and wants to know from more experienced people if it is a good idea or if it will give you problems in the long run, it is nice. But for basic database design for an application, you really need to study it and dive in.

Cheers

Eduardo Jauch wrote:

Oliver,

Being newbie in OutSystems is not a problem. You have the online trainings to learn how to program in OutSystems, and we can help you with concepts you may be struggling.

But database design is a skill by itself. If you are not skilled in database design, you will have to study it.

Otherwise, we would have to guide you through the entire process of your requirement, and this would meant we would be doing your job (without being paid for that), right?

So, if you have specific questions about how to implement a specific design in OutSystems, or you have an idea and wants to know from more experienced people if it is a good idea or if it will give you problems in the long run, it is nice. But for basic database design for an application, you really need to study it and dive in.

Cheers

Thank you so much sir. 


I will study the data modelling first. 


Hi Oliver,

I still have a hard time to grasp exactly what you want. In database design, there are multiple types of relations: 1 to 1 ("1:1"), 1 to many ("1:n"), and many to many ("n:n"). When designing the physical database, you have different strategies for each.

For a 1:1 relationship, you typically have a master Entity with an autonumber Id (the default in OutSystems), and a dependent Entity that has, as its identifier, as type the Id of the master Entity, like this:


Such a construct is typically used when there's additional data that doesn't pertain to each master Entity record, and you'd typically query it using a left join ("With or Withou" in an Aggregate).

For a 1:n relationship, the dependent Entity typically has an Attribute that has as type the Id of the master Entity, but also has its own autonumber Id, like this:

Such a construct is typically used when the master Entity can have more than one of a specific something, but that specific something is only used for that specific master.

Then there's the n:n relation. This one is similar to the 1:n, but each "specific something" is a master Entity in its own right, and can be "used" by more than one specific master. In a logical datamodel, the connection is the same as with 1:n, only with the "bird foot" connector on both tables. However, for the physical data model, and that's the data model we create in Service Studio, you need a special Entity that joins both tables, officially called an associative entity, but there are a lot of other names. This Entity contains both the Ids of the first and second master Entities, like this:

Note it can contain more than just those Ids, if it makes sense - for example, in the example above EmployeeCourse could also contain information on whether the Employee has already taken the course, and whether they gained the certificate.

I hope this makes things a little more clear, but Eduardo's advice to learn something about database modelling is definitely worth following up on.

Kilian Hekhuis wrote:

Hi Oliver,

I still have a hard time to grasp exactly what you want. In database design, there are multiple types of relations: 1 to 1 ("1:1"), 1 to many ("1:n"), and many to many ("n:n"). When designing the physical database, you have different strategies for each.

For a 1:1 relationship, you typically have a master Entity with an autonumber Id (the default in OutSystems), and a dependent Entity that has, as its identifier, as type the Id of the master Entity, like this:


Such a construct is typically used when there's additional data that doesn't pertain to each master Entity record, and you'd typically query it using a left join ("With or Withou" in an Aggregate).

For a 1:n relationship, the dependent Entity typically has an Attribute that has as type the Id of the master Entity, but also has its own autonumber Id, like this:

Such a construct is typically used when the master Entity can have more than one of a specific something, but that specific something is only used for that specific master.

Then there's the n:n relation. This one is similar to the 1:n, but each "specific something" is a master Entity in its own right, and can be "used" by more than one specific master. In a logical datamodel, the connection is the same as with 1:n, only with the "bird foot" connector on both tables. However, for the physical data model, and that's the data model we create in Service Studio, you need a special Entity that joins both tables, officially called an associative entity, but there are a lot of other names. This Entity contains both the Ids of the first and second master Entities, like this:

Note it can contain more than just those Ids, if it makes sense - for example, in the example above EmployeeCourse could also contain information on whether the Employee has already taken the course, and whether they gained the certificate.

I hope this makes things a little more clear, but Eduardo's advice to learn something about database modelling is definitely worth following up on.

Thank you Sir Killian, this is a great help.


I will try to create a data model and will post here if its correct. 

Jared Davis wrote:

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. 


Jared did you finish this database?