ajoo

Hi all, Generally when a table is split into many to enforce normalization won't it require too many inserts commands to insert that data into those tables? Won't that dictate the use of transactions, almost always? Is there a simpler way to insert the data simultaneously into multiple tables? Specifically I have a table user_details (or you may say staff details ) user_details(uid, fname, lname, dob, gender, father_name, spouse_name, add1,add2,city,zip,state,country,email,cell,phone,user_ststus) uid is the PK and all these fields come from the same form and go into this single table. If I wish to keep the address (2 address fields, city, state, zip, country ) all in the same table then is there any scope for this table to be normalized further? And now, again if I understand correct, Guru Jacques suggests that I should use the uid to create 2 more tables for the assistant_manager(user_id) and the deputy_manager(user_id) and probably use those tables for approval of staff to enforce referential integrity. I would also like to confirm if, in the table applicants(application_id, last_name, first_name, ...), applicant_id would also be the same as staff_id. (Because the user_details form is being submitted by the AM or DM after that person has approved of him / her). The first leg of approval is complete since the form is being submitted. If this is wrong then what should be the applicant id and from where should / would it originate? Thanks all !

Hi Funster ! Thanks for the reply. I'll try and learn more on DB desgin and normalization. Did not really get what Guru Jacques was trying to tell you. I am sure he will respond. But from your reply I guess he was hinting at maybe just a single table. I am not sure if my assessment of his reply is correct.er I am sure he'll revert in good time. Yes ! Guru Jacques is a multi-million dollar Guru ! Has been extremely helpful ! You too ! Thanks again Funster.

Hi Funster and Guru Jacques. Thanks for the reply to you both. @Funster : I have almost created sample tables like you indicated and was studying the relations between them. I must admit that I have very little knowledge of good table designs. In fact I have been studying and trying to understand data normalization. I do feel that my tables need a lot of that. In fact I also wanted to ask how bad is it to have tables that are not normalized. is it a trade off on efficiency or can that bring down an application. It doesn't seem to but then like I admitted, I know very little of DB design. @ Guru Jacques : I haven't really understood what you were trying to point out to Funster. I have read your reply 3 times and I am still trying to understand it. Please elaborate, if possible with a small example. More than happy to be schooled by both of you ! Thank you both very much.

Thanks funster, I figured out the FK and PK once I sat down to draw the map for the tables and relate them. I'll try out the example once I have created the tables and revert. Much Obliged. Thanks.

Hi Funster, Thanks for the examples. I'll be grateful if you can explain a bit of what you have done. I don't understand for ex what's PK or FK Please elaborate a little. Also maybe you could explain how I might be able to use these tables to accomplish the two examples quoted above from my last post. Thanks loads.

Hi Funster, thanks again for that quick response. The approval is hierarchical. It has to be approved by the AM or Dm which ever initiates the process and then go upward. All I need is to be able to design an efficient link back relation using one or more tables so that for example if M needs to look at the performance of the team of a given AM namely, 1. All DM;s of the AM and all executives of those DM's // Complex 2. All executives of the AM or in case of a DM // Simple 1. all Executives of that DM It should be easy to query the database. Any help is appreciated. Thanks ! P.S. I replied before your second reply. Will check that and revert again.

Thanks funster and requinix for the reply. Requinix's rightly said " Sounds like it requires approval from both the M and AM. So two flags, ". It's a kind of double check you may say. Actually I am not exactly sure at this stage what data would be involved for performance. So I just took one bit of data P just to know how that one parameter would be handled and it would give me the idea to handle other data. Sorry that's a mistake on my part. No the manager M will not directly recruit the executives. Once again right now I am only interested in the minimum fields that would Grant the user Authorization ( a flag ) as well as take into account the user granting the authorization. @ Requinix. Very true Requinix. The idea is to backtrack the hierarchy to the Manager in each case. I had the simple case in mind like this :- ExecID AP_M AP_AM AP_DM ================================== 11 1 1 1 recruited by DM 12 1 1 recruited by AM but this does not really backtrack the hierarchy. It just shows who all approved rather than who all had to approve. For eg in the second case how can anyone know just by looking at the data that the whether the DM has not approved or the approval was not required. I hope I am able to explain myself. Besides if only one in a hundred cases does a DM recruit a franchisee, it will result in an entire column being wasted. (I think the term is that data is not normalized). Kindly help me grapple with this. Thank loads.

Hi all, I wish to define a hierarchical relation in a table or set of tables. I'll use an example to describe the problem. In a hotel there is One Manager(M), Assistant Managers(AMs) and a Deputy Managers(DMs) and each of them can recruit executives for the hotel. 1. The Manager(M) can recruit the AMs and the DMs. 2. Further the AM can recruit a DM which must be approved(AP_M) by the Manager. // AP_M -> field for approval by the Manager 3. The AM can directly recruit executives and they must also be approved by the Manager.(AP_M) 4. The DM can recruit executives and they must be approved by the AM (AP_AM) and the Manager (AP_M). // AP_AM -> approval by AM Further : a) The Manager can view the performance(P) of all the AMs, DMs, and executives in the hotel whether recruited by himself or by the AMs or DMs. b) An AM can view the performance (P) of all the DMs that he recruits, all executives recruited by his DM's and all executives recruited by the AM directly. c) The Manager cannot recruit executives. That is done by the AM and DM. Please can someone help me define the table/tables and help implement these relations in them. Thanks all!

Hi Guru Jacques, hmmm ofcourse you are right !

Hi all !, I was just wondering if it's possible to autoset the value of a field in mysql table based on the value of another 2 fields values. for example if I wish to implement AND logic on some fields : F1 F2 F1_F2 ======================== 0 0 0 1 0 0 0 1 0 1 1 1 ===================== If I have columns F1, F2 & F1_F2, can the mysql database auto set the value of the F1_F2 based on the values of F1 and F2 as in the table above. If so kindly show how it can be done. Please note that the values of F1 and F2 will be set programmatically using PHP. Thank You.

Thanks Guru Jacques for that insight on other security areas. I would like to clarify if it would be OK to have the captcha ON from the very beginning on these pages since we do not want the user to know if the given email ID is registered or not. I mean we don;t wait for n number of false tries before the captcha is displayed, we use it for every request. Thank you.

Hi Guru Jacques, Thanks for the reply. I must be getting paranoid about security. Now that you mention it and I am thinking about it I am not so sure which threat I am worried about. I think I had in mind someone impersonating the pages related to password resetting, be it the password email request page or the page in response to that request. Since these pages are public and can be accessed directly I was getting a bit worried. If that's OK so long as the form and fields authentication / validation is good and if you think there is nothing else that needs to be looked into then I am good. Thanks.

Hi all ! I would have liked to continue this question on my previous post but since it became too long I thought I'ld post a new one. I would like to add the following bit of code on my reset page $current = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI']; if(isset($_SERVER['HTTP_REFERER'])) $referrer = $_SERVER['HTTP_REFERER']; if ( $referrer === $current ) { }else { } to ensure that the page is being called from where it should be called. Is this OK or is there a better ( read more secure ) way to do it? (I think I read in one of the posts, quite some time ago, that this was not altogether secure). Since the password reset page is publicly accessible what other security concerns can turn up because of that and what care should be taken. Thanks all !

Hi Psycho, Thanks for the advice. I think you are referring to a second channel of authentication like maybe via an sms. As of now, I would be more than pleased if I can just get this logic to be implemented correctly. Certainly I would like to implement that at a later stage maybe. I have that in mind already. Thanks again !

Thanks funster for all the help and discussion & for being such a sport

Hi Guru Jacques and Funster, Thanks for the replies and the discussion. Your discussion on the various techniques will go a long way to clear the doubts of many New comers like me, especially for issues that are related to security so closely. Thanks very much. I think I will stick with the advise offered by Guru Jacques since that relates more to the security of applications on the internet. I think I have made up my mind, with the input from all who replied to this, to keep it simple, all in one table. This is how I think I will do it: Have just one field for the hashed token and time stamp which I guess can be used both for account activation and password reset. There can be no confusion in the tokens since the password reset can only take place after the account activation. Have 2 boolean fields: one for account activation and one for password resetting. The password reset field can be toggled for each valid password reset request. And later reset back once the password has successfully changed. Once the account is activated i'll change the related token field to null. Same for password reset. So that would be a total of 4 additional fields in the table. If there is still a flaw here in the logic, kindly alert me. Thanks a lot every one.

Thanks Guru Jacques & Funster for the inputs. Funster if you don't store the tokens, then what does the link compare to once it's been clicked by the user? I believe it's not possible to retrieve the data once it has been hashed. Any particular reason for for being so averse to storing tokens. Guru Jacques, I'll read up on the link and revert. Thank you both.

Thank you Guru Jacques for clarifying the subtle but huge differences between the two procedures. Are you suggesting that checking and changing (resetting to NULL once used) the token be an atomic operation? I have also tried to use a bit of logic which prevents multiple tokens from being generated till a certain time period has lapsed. I was thinking of keeping the tokens and its time stamp in separate table and updating the account activation field in the users table once the token had been authenticated and also deleting that row with the token and time stamp from the table (with just token & ts ) all at one go. Reason being that 1. the tokens once used are not needed and are in fact a security hazard if kept. 2. only relevant data is stored in the users table preventing clutter. After psycho advised against using the delete operations I am thinking of forgetting about the deletions and just setting the tokens to NULL. Please advise. Thank you.

Thank you Psycho for that detailed information especially about the delete operations. I'll have to decide about using or not using them but as of now I think i'll just let them be. Thanks

Hi ginerjm, Thanks for the revert. Since the token, once verified & used is no longer required, so I thought if I would create a new table (say temp ) to keep that data ( token with a time stamp) and I would update the activate field in the users table ( the main table with user details. ) Once it's been activated ( for account activation ), the values in the temp table are no longer needed and so that can be deleted. In fact I delete the record as soon as the activation has been achieved. I have no idea so I asked. If I forgot my plain text password and google has no way of knowing it since they don't store plain passwords, how would an old password help? Probably they have the hashes of old passwords stored. So once again, would this be a good or a bad idea to delete the records of the temporary tokens and timestamps for the same. Thanks loads !

Hi all ! It looks to me that sending an activation email and resetting a password are more or less similar operations as both require a token to be returned to the website for verification and thereafter changing the password. The two cannot be confused since password resetting must obviously always, if at all, occur after account activation. The record in the database would in any case be deleted after any of these operations have been executed successfully. So I was wondering if it is alright to use the same table in the database for both these operations. Or do we need to retain some information in the database after these operations are completed. Information that can be handy later for some operations I cannot think of right now. I have noticed that google asks for any old password that a user can recall. What do they do with that? How can they identify a user with that I wonder ? They wouldn't be storing plain passwords would they? Thanks all !

Thank you Guru Jacques. Happy to take your advice always !

By numeric I mean the integer and float values. Values stored in a DB, auto increment values etc. I think, I am almost sure, that I have used the number value for all the number inputs but I'll recheck that. What about filter_validate_number? is that a good option too? Thanks !

Hi Guru Jacques, I think I have asked this before but since I could not find your reply, I'll as ask it once again. For escaping HTML output you suggested a great function html_escape(), that I can use to sanitize all strings. The question is how to validate a numeric output. For example if there is a form field which expects numeric input then we need to check that the input is indeed numeric. Would using the is_numeric() function be sufficient for this purpose ? Anything else that we would need to take care of ? Thanks !

Hi Guru Jacques, Thank you for the response and sorry for the delayed reply. The echo in the code was only for testing the loop traversed but I get the point. Thanks and will come back for more!