Because in my application it is VERY VERY VERY important that I track the specific details for any given user in any given account. The user data changes frequently and I need to be able to track user information changes, numbers of accounts they are assigned to etc. So rather than updating the user information or keeping one user and tying them to multiple accounts it is easier to maintain a specific user per account for our strict tracking needs. This way I can view all accounts the user is associated with, track user information changes over months/years and provide detailed statsical reports to auditors. On 3/20/07, Jim Lucas <lists@xxxxxxxxx> wrote:
Dan Shirah wrote: > I had thought about having the multiple submissions on a single form, but > with the amount of user information that is collected and the variable > amount of users that may need to be entered this method would not be ideal. > (A single form that you have to scroll down a far way and the potential to > not have enough user inputs is what prevents this method from being used) > > Each account is unique. It is possible that the same user could be on > multiple accounts, therefore the account will be tied to the user each > time. > > No account_id will be duplicated, but the user table can hold the same user > multiple times tied to different accounts. Why duplicate data? Why not do it as suggested? What do you mean by "not ideal" ?? > > > On 3/20/07, Greg Beaver <greg@xxxxxxxxxxxxxxxxx> wrote: >> >> Dan Shirah wrote: >> > In my database I have two tables. One table stores all of the account >> > information, the other table stores all of the user information. >> > >> > Table 1 >> > account_id - is the primary key and is unique to each account >> > >> > Table 2 >> > user_id - is the primary key and is unique to each user >> > account_id - will be set to whatever the account id is in Table 1 >> > >> > You are suggesting that when they click "Enter another user for this >> > account" that I save the account/user information to the database and >> > then when the form refreshes have something like: >> > >> > if (!isset($_POST['submit'])) >> > >> > to determine if this is a continuance of a multiple user submission? >> > >> > And then I should select the account_id that was just created? Using >> > something like: >> > >> > SELECT scope_identity() FROM accounts >> > >> > Or is there a better way to determine the recently created >> account_id so >> > I can use it for any other user data that is created for this account? >> >> Hi Dan, >> >> Yes, there is a better way. What you have described is probably more >> complex than you need, and requires more clicking by the user than is >> necessary. >> >> First of all, do you allow multiple accounts for the same user? If so, >> you're going to run into troubles with querying later on with the >> current data schema. Generally, when I'm working with inter-linking >> data like you describe, I construct it as: >> >> Table 1 account_info: >> account information >> account_id >> >> Table 2 user_info: >> user information >> user_id >> >> Table 3 account_link: >> user_id >> account_id >> >> This way, you never duplicate user information, and it is very easy to >> link or unlink users from accounts. >> >> In the web form, if you allow adding up to 3 users maximum, why not just >> put all of the input on a single page? >> >> [html] >> Account information: >> >> Form Field 1: ________ >> ... >> >> User #1 (required) >> >> Name: _____ >> ... >> >> User #2 (optional) >> >> Name: _____ >> ... >> >> User #3 (optional) >> >> Name: _____ >> ... >> [/html] >> >> In addition, you might consider either using an AJAX-based dropdown, or >> populating a static dropdown with previous users (assuming the list of >> users is short), so that if a previous user is entered into the account, >> that name can be used. >> >> In addition, if you have all 3 users on one page, you can validate them >> all at once, check for accidental duplicates (i.e. if a name matches an >> existing user, bring up the form and ask if they intend to create a new >> user, or wish to use the existing one). >> >> There are lots of options. If you do decide to do a multi-page form, >> with database submit right at the last step, there is an example of how >> I've done this in the code for pear.php.net's election creation >> interface, at >> >> http://cvs.php.net/viewvc.cgi/pearweb/public_html/election/new.php?view=markup >> >> . >> >> >> This code makes use of simple php-based templates found in: >> >> >> http://cvs.php.net/viewvc.cgi/pearweb/templates/election-new-step1.tpl.php?view=markup >> >> >> http://cvs.php.net/viewvc.cgi/pearweb/templates/election-new-step2.tpl.php?view=markup >> >> >> http://cvs.php.net/viewvc.cgi/pearweb/templates/election-new-step3.tpl.php?view=markup >> >> >> Hope this helps, >> Greg >> -- >> Experience the revolution, buy the PEAR Installer Manifesto >> http://www.packtpub.com/book/PEAR-installer >> >> > -- Enjoy, Jim Lucas Different eyes see different things. Different hearts beat on different strings. But there are times for you and me when all such things agree. - Rush
