Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Tips on a good design 1

Status
Not open for further replies.

Itshim

Programmer
Apr 6, 2004
277
US
Here's the rundown:
I am currently working on a project where 'members' have the ability to create a profile of themselves (but are not forced to). The profiles need to be approved before they are 'active'. So I came up with three table:

member (parent_key: member_id)
profile (parent_key/foreign_key: member_id)
on_hold (parent_key/foreign_key: member_id)

If the profile is denied it must still remain in the database for an extended period of time, so I was thinking about adding a 'status' column in the profile table, but here's the rub. If a member updates their (currently approved) profile it must go through the approval process again. Since they have an approved profile, I want to keep that profile active while the update goes through the approval process (this completely destroys my three table design).

As for an added complication the profiles are fairly extensive, about 30 values (columns) make up a profile, and these profiles will need to be searched as quickly as possible, which lead me to the idea of separating the approved profiles to their own table; I guess keyed on 'member_id', so during a search, the query would only need to search through approved profiles and not deal with the status of a profile, but I'm really not sure of the best way to keep all the data without having a lot of crap in sveral tables.

So it all boils down to 'approved', 'denied', and 'on hold' profiles. Members can have an approved profile and an 'on hold' profile at the same time, and denied profile must be stored (for a period of time), plus be quickly searched.
 
Make an independent table for the profiles, and give them each a member_id and a Staus flag. With 3 possibilities. Approved,in process, denied.

When you search the table to display the approved tables you select will find the profile that matches the ID and also has the flag set to approved.

Once the other profile is approved, you can run a query to delete the old profile, and change the flag of the new profile to approved. That way it will get used the next time it is requested.

As for the denied profile you keep it there for whatever length of time you require and if it needs to be altered you can access it also.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Just so I understand you. Make a 'profile' table which contains the 'profile', member id, and status, and select from there based on the status flag and member_id.

If I that is correct, the table will be huge. If the site has 100,000 profiles then you would expect it to have around 150,000 profiles including denied profiles and in process profiles. Will this still be efficient for searching?
 
MYSQL can handle that ammount of data easily.

Having more tables and linking them together with 50,000plus records is bound to take more time than looking in the same one.

Searches with at most two criteria, should not take very long.
SELECT *FROM profiles where member_id=x and status=1.

There is also query caching to spped upo queries.

All in all 150,000 records should pose little problem for a MYSQL.


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Thank you for the clarifications; I have problems determining when one large (or what I consider large) table is better, or several small tables will be more efficient.

Case solved: As in the words of Hercule Poirot, "Bon!".
 
Glad you sorted it out.


"I, who have undoubtedly the finest brain in Europe at present, can afford to be magnanimous!"
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
a large table is more efficient to search than several small ones.
100,000 - 150,000 rows would still be considered on the small size for a database table.

when you are around 10,000,000 rows or so that is a medium table size.

make sure the columns you are doing the significant number of joins on are indexed. that will speed up your queries noticably over non-indexed tables.
 
Thank you for the extra info guelphdad. Luckily my boss has no problem with storage space, so I analyze all my queries with 'explain' and make the appropriate indexes as a general rule.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top