My sons are starting an Internet business and they have resurrected me from retirement to develop the backend for the Web site. The Web developer will be using MySQL on Unix. I have no experience using MySQL or Unix, so I'm using Access which will be converted to MySQL when I'm done.
So here is my question:
I have a table called tblUsers that contains all the usual User related fields such as strUserName, strUserFirstName, etc., etc. It also has a field called lngReferralSource that indicates how the user was referred to the site. If the user was referred by another user (whose name would also be stored in the tblUsers table) then the referring user's strUserName is stored in a field in the called strReferringUser. I have accomplished this by creating a lookup (using an SQL statement) which links the strReferringUser field to the strUserName field (both of which are located in the tblUser table). This, of course, results in a dropdown box in the table for the strReferringUser field which allows the selection of the referring user's strUserName to be stored.
Next I need to track the number of times each user refers another user to the site. I have done this by creating a query that groups on the strUserName and counts the strReferringUser.
All of this works just fine in Access, but will it work in MySQL? Will MySQL recognize the self-join I have created for the referring user? Can the query be converted? What limitations do I need to be aware of as I'm creating the database so that the conversion will go smoothly.
Any help or suggestions would be so greatly appreciated.
Thanks in advance