Query woes :confused:

[ajoo]

Hi benanamen!

 

 I value all advice but it would help to know what makes you so sure that there is a design problem in this layout?

 

In fact the layout of the picture is the blue print. Maybe you can elaborate and show me how you would build such a blueprint for a simple recruitee - recruit application.

 

The rules are simple as I told you that it's hierarchical in nature. You can see them clearly in the layout as well as the sql dump. 

 

If there is something specific that you have in mind   , then please ask and i'll try and answer my best. 

 

Thank you !

Edited August 25, 2016 by ajoo

[ajoo]

Hi benanamen !

 

I think by rules you mean the rules of recruitment. If that is what you were asking, they are as follows:

 

                                   BOSS

                                  /          \

                                 /            \

                           Admin         Master

                          /          \                   \

                 Franch         Master         Franch

                                          |

                                      Franch

 

Thank you.

[benanamen]

I am going to leave this to someone else. Do you really think someone could build a database application from the information you provided in this thread? And I don't think the DB is wrong, It just is.

 

* A flow chart is not business rules.

* The picture is NOT a blueprint. It is your incorrect attempt to do whatever it is you're trying to do which you have far from made clear.

* "simple recruitee - recruit application." Who the heck knows what that involves. You clearly have not said.

* "The rules are simple as I told you that it's hierarchical in nature." - Completely meaningless. No one could build anything from what you have said and provided.

 

I have no desire to continually have to drag information out of you.

[ajoo]

Hi Benanamen, 

 

Thanks for the reply. I don't know why you are so upset.

 

I complied with all that you said. I tried to provide all the information that I thought you would require. In fact my last mail clearly shows the rules and I provided that without you asking it because I realized late that you might require it so I sent it in a separate message. I also said that if you need to know anything specific then you could ask and I will try my best to provide it. 

 

It's OK, we can forget this. No need to be upset. 

Take care & thank you. 

[benanamen]

Not upset at all. Lets try this again. Forget that you have any code or database and you were hiring someone to build the application.

 

Provide a detailed explanation of the project and requirements.

 

 

All I know so far is it is some sort of "recruiting" app. Recruiting who for what? Who is recruiting who? What rules or restrictions are their on the recruiters and the same for those being recruited and so on. What are ALL the parameters for this app?

Edited August 25, 2016 by benanamen

[ajoo]

Hi Benanamen !

 

Glad that you are not upset.

 

I have given the recruitment rules in the diagram above. Namely :

-the Boss recruits the Admins & Masters.

-the Admin recruits Masters & Franchisees,

- the Masters recruit Franchisees.

 

Each recruitment require that they be confirmed by all up the hierarchy. If a recruit has all the confirmations up the hierarchy then they they may be placed in their own tables as confirmed recruits.  

 

That's about it. Please ask for any specifics that come to your mind. 

Thanks.

[benanamen]

Does each level only recruit the level directly below them or all levels below them?

 

Example: Can a boss recruit a franch or only a master and admin? 

 

What happens if a recruit does not get all the confirmations?

 

This is what I mean by rules and details.

[ajoo]

Hi Benanamen,

 

Yes each recruits directly the ones below it. So the Boss does not recruit the franchisee. In case the recruits do does not get all their permissions, their ID is not inserted into their individual tables. ( Check the picture- there are three table there at the bottom ).

 

Thank you

[benanamen]

 

 

In case the recruits do does not get all their permissions, their ID is not inserted into their individual tables.

 

Whatever your doing, inserting an id into individual tables is just wrong. You don't seem to grasp how to provide the information needed for someone to help you with this, so I am going back to passing on this. Good luck.

[ajoo]

Hi Benanamen !

 

Thanks for your inputs, suggestions and effort. 

No sweat ! We'll pick it up some other time. 

 

Thank you. 

[Jacques1]

Whatever your doing, inserting an id into individual tables is just wrong.

 

It don't think so. It's a model of the data.

 

Since there are specific rules for who can recruit whom, a recruitment is not just a simple person-to-person relationship. A lowly franchisee cannot recruit a boss. A master cannot recruit another master.

 

You can pretend that anybody can recruit anybody and then implement the rules with triggers. This has been suggested in the previous thread. The problem is that triggers are obscure, and until now, nobody has volunteered to implement them. Or you could accept arbitrary data and then try to clean it up in the application, but I don't think that's a a good idea.

 

So you won't get around the fact that there are different types of recruitments which involve different types of roles.

[Barand]

Glad you dropped by this thread, Jacques. Can you explain to us (ajoo isn't making it very clear) how your data model shown in reply#22 above implements the required checks and constraints for this process?

[benanamen]

Admittedly, I am far from clear on what he is doing with the limited information he has provided in this thread. Looking at the image in post #22 it just doesn't look right for a DB design.

 

The comment was in direct response to the 3 tables at the bottom of the image. Admin, Master, and Franchisee which all have a single column with an ID from who knows where or what it is supposed to mean. You're not really saying that is correct are you?

 

Additionally, the OP's sql dump from post #24 has no keys tying any of the data together and then there is the negative values in table Master_Reqruitment. What in heaven's name does that mean?

 

I have no idea what information was given in some unlinked thread.

 

Could you, @Jaques1, build a proper DB application based on this thread?

Edited August 26, 2016 by benanamen

[Jacques1]

Glad you dropped by this thread, Jacques. Can you explain to us (ajoo isn't making it very clear) how your data model shown in reply#22 above implements the required checks and constraints for this process?

 

Sure. I can at least explain the ideas which do come from me.

 

admin, master and franchisee are subsets of the staff table with the ID being a foreign key. The three recruitment tables cover the following three cases:

• The boss recruits a staff member as either an admin or a master. The “recruitment” can be deactivated and reactivated at any time (it's really supervision, not a recruitment).
• An admin from the admin table can recruit a staff member as either a master or franchisee. The recruitment can be (de)activated by both the admin himself and the boss.
• A master from the master table recruits a staff member as a franchisee (there are no other options). This relation can be (de)activated by any of the involved recruiters (the master, the admin who recruited the master, the boss).

The role column can come in conflict with the role tables (e. g. a staff member recruited as an admin won't be able to recruit anybody until he's inserted into the admin table). This could be fixed by further splitting the recruitment tables.

 

I can't comment on the SQL dump.

Edited August 26, 2016 by Jacques1

[ajoo]

Hi Guru Jacques, Guru Barand & Benanamen,

 

@Guru Jacques ! To repeat what Guru Barand said, I am also very glad that you dropped by this thread.  I tried to explain as much as I could, the rules and everything, with diagrams that I made, but I am pained if by "Ajoo isn't making it very clear", Guru Barand is implying that I was doing that on purpose. I tried my best to provide all the information.

 

Glad that you could explain it to here like only you could have. 

 

@ Benanamen :- 

 

 

Additionally, the OP's sql dump from post #24 has no keys tying any of the data together and then there is the negative values in table Master_Reqruitment. What in heaven's name does that mean?

 

The tying up takes place in the tables Admins, Masters and Franchisee when the permissions have been all granted in the recruitment tables. Those are not there in the SQL dump. I thought that was amply obvious because of the simplicity of the table. I was wrong. You could have asked again. The +ve and -ve values are a system of weights to determine if all permissions have been provided. The -ve values are used to deactivate or revoke permissions.  

 

@ Jacques

 

 

The role column can come in conflict with the role tables (e. g. a staff member recruited as an admin won't be able to recruit anybody until he's inserted into the admin table). This could be fixed by further splitting the recruitment tables.

 

SIr if you can elaborate on this I will be grateful. 

 

Thanks again Guru Jacques, Guru Barand and Benanamen. 

[Barand]

That you were not making it clear was a statement of fact. It was not a statement of blame that it was deliberate, rather one of inadequacy.

 

You gave us four tables and claimed that the hierarchical relationship was obvious. Well, without indicating the relationships between those tables, it wasn't. In the tables you have "aid" and "mid" columns, which look like foreign keys, but your inadequate naming convention gave absolutely no clues about what they referenced.

 

And your pyramid diagram was a complete mystery until you later told us what it represented. Until then we had to guess.

 

Plus there are other mysteries in there that Benanamen has indicated.

[ajoo]

Hi Guru Barand, 

 

I hope that in my last reply I have cleared all the mysteries that benanamen was referring to. If there is still something that you would like to ask about I'll be happy to answer the best I can.

 

Thanks.