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.
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.