• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

Database Design (Friendship)

Personal Table
Personal_ID (PK), First_Name, Last_Name, Gender, DOB, Address, City, State, Zip, Country, Phone, Photo, Account_ID (FK)

Matching Table
Matching_ID (PK), Headline, Description, Purpose, Relationship_goal, Maritial_Goal, Maritial_Status, No_of_Children, Race, Religion, Primary_Language, Other_Languages, Personal_ID (FK)

Physical Table
Physical_ID (PK), Height, Weight, Body_Build, Hair_Color, Hair_Length,Eye_Color, Health_Status, Disabilities, Personal_ID (FK)

Contact Table
Contact_ID (PK), ICQ_UIN, Homepage_URL, Personal_ID (FK)

Statistic Table
Statistic_ID (PK), Hits, Points, Personal_ID (FK)

Detailed Table
Detailed_ID (PK), Hobbies, Hangout, Drinking, Smoking,
Education_Level, Occupation, Industry, Monthly_Income,
Private_Messages, Personal_ID (FK)

Photos Table
Photo_ID (PK), Personal_ID (FK), Photo

Verification Table
Verification_ID (PK), Email, Country, Verification_Code, Status

Account Table
Account_ID (PK), Username, Password, Confirm_Password, Password_Reminder, Email

Error_Log Table
Error_Log_ID (PK), Description, DateTime


Explanation:

Verification table - Is responsible for storing the verification information. Before a user can sign up to become a member, he/she is required to submit their e-mail add and country info. This information will then be stored in this table together with the verification code generated using a series of algorithm. The status field will be updated with a value if the registration has been
completed, or else it will be set to another value (cancel). An e-mail is send together with a link that contains the query string for e-mail n verication_code.
When the link is clicked, the information parsed as a query string is processed and a form will appear with fields, whereby the user is required to fill in like
account information, etc etc.

Statistic table - The statistic table works simular like how experts exchange assign points(-/+) to each member.

My focus is on the database design itself. I would like to make it as efficient as possible with your help (experts), thru comments, suggestions and etc.

Your help is kindly appreciated.

Regards
Eugene
0
eugene007
Asked:
eugene007
  • 16
  • 8
  • 3
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think that the design seems fine so far, there are optimizations done regards the physical implementation, and relations are good.
The design of the Detailed table is a choice: you might find some restrictions/problems in the queries or flexibility afterwards.

For efficiency, the design as such seems "perfect". For the physical implementation, you will need to add some indexes, which are determined by the most costful queries. The PK will anyway have an index, but some other fields can be indexed. This can only be found by filling the database will data, and running the application code over it. You will see if the queries perform fine, or if tuning is needed. Don't forget that tuning is generally an ongoing process.

CHeers
0
 
drittichCommented:
I agree, looks very good.  I would also index all FKs, and the username/password fields, as they will be used frequently.
0
 
eugene007Author Commented:
But from this design, If lets say a member decides to terminate his/her membership, when the deletion is done on the personal table, all the referencing table will be deleted as there is a foreign key in them. However for the verification and account table there will be a problem.
What would u recommand me to do in order to solve this problem?.  
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
eugene007Author Commented:
Do u see any problems when it comes to insertion, deletion, updation?. I mean normally to me it looks ok, but when viewed by someone else the error can be seen more clearly.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
The "delete" of information in database and the logical delete are different concepts. In fact, when the member decides to end membership, this doesnt mean that you should DELETE his records from the database straight away. You need to indicate these things in a status field, or a field that indicates the end-of-membership. This would mean that this account is closed, but you shouldnt delete it until all the things to do have been done, and it could be archived.
CHeers
0
 
eugene007Author Commented:
But if I place a status field indicating that the member has terminate his/her membership, later on if this member attemps to register again, dont u think that there will be a duplication of data and un-necessary utilization of space. Secondly, I have a verification table, whereby a person who has already submited his e-mail ID wont be able to submit his e-mail ID again. That will also be part of the problem.
0
 
eugene007Author Commented:
I hope to receive more comments, suggestions n etc.
0
 
eugene007Author Commented:
Another thing, is storing all those deleted record into a temporary table be advisable (for future reference).
0
 
eugene007Author Commented:
"but you shouldnt delete it until all the things to do have been done, and it could be archived."

Referring to this statement, can u plz explain to me further?.
0
 
drittichCommented:
>But from this design, If lets say a member decides to terminate his/her membership, when the deletion
>is done on the personal table, all the referencing table will be deleted as there is a foreign key in
>them. However for the verification and account table there will be a problem.
>What would u recommand me to do in order to solve this problem?.  

As angelIII suggested, use a status field, e.g., where a value of D means deleted

>Do u see any problems when it comes to insertion, deletion, updation?. I mean normally to me it looks
>ok, but when viewed by someone else the error can be seen more clearly.

No

>But if I place a status field indicating that the member has terminate his/her membership, later on
>if this member attemps to register again, dont u think that there will be a duplication of data and
>un-necessary utilization of space.

Do you really anticipating this happening that much? How much space are we talking about?  A few MB at the most I would think. I would create archive tables where all users' records that have a status of D for longer than 6 months, for example, are moved to the archive tables, which are replicas of the existing tables.

>Secondly, I have a verification table, whereby a person who has already
>submited his e-mail ID wont be able to submit his e-mail ID again. That will also be part of the problem.

I am not sure why that is a problem. If someone re-registers, they get a new Personal_ID.  It is a new record, a new person for all intents and purposes, as far as the system is concerned.
0
 
eugene007Author Commented:
What would u recommand me to do for the verification table?.
0
 
eugene007Author Commented:
Another thing, between the account table and the personal table. Is it the right procedure to insert details into the personal table first and then only insert a record into the account table.
0
 
eugene007Author Commented:
The verification table seems to be independent. There is no foreign key in it. That could be a potential problem.
what is your point of view (based on your experience) regarding about this matter?.
0
 
drittichCommented:
>Another thing, between the account table and the personal table. Is it the right procedure to insert details into the personal table first and then only insert a record into the account table.

No, account table first, as you need the pk, in order to insert it into personal table.

>The verification table seems to be independent. There is no foreign key in it.

Add an Account_ID(FK) column to it.  
0
 
eugene007Author Commented:
well regarding about the verification table, data will be entered first in this table before the user can go on with further registration. So if I were to add a account table foreign key in it, that would affect my data entry. I Hope to receive your comments for this area.
0
 
drittichCommented:
Well, you know the order your are doing things in, so do what seems right!
0
 
eugene007Author Commented:
he he ok.
0
 
eugene007Author Commented:
he he ok.
0
 
eugene007Author Commented:
another thing, would u use a database to keep track of the number of users currently online or would you use a session variable to do so?.
0
 
drittichCommented:
that is off-topic, time for a new question
0
 
eugene007Author Commented:
well would anyone care to give me 100 points free :)
0
 
eugene007Author Commented:
Another thing would you recommand me to have a Error_Log Table. Will the following columns stated bellow be sufficient enough for recording the error details.

Error_Log_ID (PK), Description, DateTime

0
 
drittichCommented:
I am not sure that this question will ever end...
0
 
eugene007Author Commented:
every beginning has an end to it.
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
drittichCommented:
I feel I should get the points on this one.  I answered several questions here.
0
 
nico5038Commented:
I can agree, but from angelIII there are also some valuable comments....
I just find "design issue's" hard to judge as there are so many aspects and "roads".
I can find myself in a pointsplit between you and angelIII, but would like to have his comment too.

Nic;o)
0
 
drittichCommented:
You are absolutely right, I forgot which question this was...

A split or give em all to angel...but someone should get points, a lot of effort here.
0
 
nico5038Commented:
Then I'll propose to the moderator to:

1) remove the points here and
2) paste two "Points for " Q's for drittich (30) and angelIII (30)

Nic;o)
0
 
NetminderCommented:
Per recommendation, points NOT refunded and question closed. "Points For" questions will be posted as soon as the system allows.

dittrich and angel111: please watch this question for you points splits.

Netminder
CS Moderator
0
 
NetminderCommented:
dittrich: points for you at http://www.experts-exchange.com/Databases/Q_20369843.html
angel111: points for you at http://www.experts-exchange.com/Databases/Q_20369815.html

Netminder
CS Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 16
  • 8
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now