I am currently in the process of designing my database for a mobile/web application. When I look at competing apps I see a massive flaw in how they handle user locations. Due to the nature of the application this could cause privacy and security issues for the users.
I need to store all user's locations in my database, but I want to reduce the possibility of an outside source gaining access to this data if the user does not want it shared.
After researching possible ways of doing this, I believe it is in my best interest to return values through stored procedures, limit db user access etc.
What I want to achieve is returning the results of a stored procedure. After the results have been modified. In this case, the procedure will run through the results. Check a value on the record and omit the relevant data if required.
From what I have read and understood the best way for me to achieve this is through creating a temporary table. Run a Select Query on my actual table into a cursor. Loop through the results, check the relevant value in the record, the insert into the temp table accordingly.
Begin Procedure Check if temp table already exists, drop if necessary Create Temp Table, based on schema of the "permanent" table. Run SELECT * FROM PERMTABLE WHERE Location within bounds ORDER BY Location ASC INTO CURSOR LOOP THROUGH CURSOR IF 'Show_Loc' IS FALSE INSERT INTO TEMPTABLE WITHOUT LOCATION ELSE INSERT AS NORMAL SELECT * FROM TEMPTABLE DROP TEMPTABLE END
Is there a better way of achieving this, or am I on the right track?
Also I am concerned that there will be a large volume of requests to the same procedure. Will there be any issues if the procedure was called multiple times at the exact same time in regards to temporary tables? Do I need to generate random names, or do MySQL/SQL Server handle this automatically?