db design confusion -- normalization

keyur

hello,

I just happened to visit ur website. I am looking around for some help on a db design which I am working on.

I am working on a dating site, I have 5 Tables in which I am storing the profile data of a member.
The tables are as follows:
1. MemberInfo - login and billing details.
2. MemberProfile - all the details which are in a profile like education, gender, age etc.. kind of fields. It has MemberID as a foreign key
3. MemberInterests - the personal interests of the members are stored in this table. It has ProfileID as a foreign key to MemberProfile table.
4. MemberSeekingInterests - similar to table 2. It has ProfileID as foreign key.
5. MemberPhotos - which stores 5 photos of each member, has a foreign key. ProfileID.

In the MemberProfile and MemberPhotos I have a boolean field which holds the value whether this record is approved by admin.

Now my actual problem is that whenever the member makes changes or adds in the profile, it is to be approved by the admin first then only it will be displayed in the system.

The point is that the member's profile should be available in the results even if he makes changes.

I am considering to use any of the following options :
1. To maintain two records in the MemberProfile and MemberPhotos for the same profile and same photo so that when one record is modified it will go for the admin verification (a boolean flag will be set to AwaitingVerification=TRUE) and in that time the other record will be visible in the search results.
2. I am planning to create two sets of tables. The first set as I mentioned above and other set which will hold the temporary data. And all the admin verification will be done in the temp tables. If verified the data will be updated in the actual tables from the temp and then deleted in the temp.

Pl. let meknow which is the best option (from the normalization and performance point of view).

We are expecting the database to be a very large data with millions of records. Pl. advice

drawmack

yeah I would make a change add a new record and admin approval delete the old record. Then just throw an auto number field in there and you can display the record with the lowest auto number.

Just make sure you only make two records in a case like this:

User Edit's Profile (record created)
Admin Disapproves Profile and I presume tells them member why (no database changes)
User Edit's Updated Profile (no record created)
etc.

Basically it has to work like this

if the user has 1 profiile or image in the database an edit creates a new recrod.
if the user has 2 profiles or images in the database they can edit either record but the changes go into the new record and do not create a new one.

this is some pretty complex logic but it's doable.

tomhath

I'd go with the second option, a separate set of tables to temporarily hold data that hasn't been approved yet. That completely separates the display tables and logic from the update/admin tables and logic.

Your queries will be simpler and less error prone without all the flags and internal self-joins you would need with the two-rows-in-one-table approach. Not to mention the race conditions you'd run into updating one row and then deleting or updating the second row in the same table.

keyur

thank you for your effors,

drawmack, yes it is doable.

but my major concern is the performance.

I am really worried about the option 1 because I doubt it may face problems with millions of records.

tomhath, my colleauge is tending towards option 1 and I personally feel that option 2 will be best.

I would like to know what will be best option.
Have you faced this kind of problem in the past?

pl. advice.

tomhath

Most of the time I've seen Option 1 used; it's so tempting to just add a couple of flags and think you've really saved something by not adding another table to your database. But where's the saving? You still have just as many rows; instead of two smaller tables you have one big one with the rows shuffled together.

And it seems to me that staged data is a different entity in your data model than approved data (read up on things like entity relationships and ER Diagrams or state transitions and Finite State Machines).

But I'd worry less about the table structure than the added code complexity. Every stinkin' query against that table would have extra junk in the WHERE clause to pick the proper rows.