Solved

Database Design (Friendship)

Posted on 2002-06-17
31
328 Views
Last Modified: 2008-03-06
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
Comment
Question by:eugene007
  • 16
  • 8
  • 3
  • +2
31 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7086550
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
 
LVL 8

Expert Comment

by:drittich
ID: 7088025
I agree, looks very good.  I would also index all FKs, and the username/password fields, as they will be used frequently.
0
 

Author Comment

by:eugene007
ID: 7088282
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
 

Author Comment

by:eugene007
ID: 7088592
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7089059
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
 

Author Comment

by:eugene007
ID: 7089285
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
 

Author Comment

by:eugene007
ID: 7089295
I hope to receive more comments, suggestions n etc.
0
 

Author Comment

by:eugene007
ID: 7089310
Another thing, is storing all those deleted record into a temporary table be advisable (for future reference).
0
 

Author Comment

by:eugene007
ID: 7089495
"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
 
LVL 8

Expert Comment

by:drittich
ID: 7089843
>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
 

Author Comment

by:eugene007
ID: 7089941
What would u recommand me to do for the verification table?.
0
 

Author Comment

by:eugene007
ID: 7090103
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
 

Author Comment

by:eugene007
ID: 7091182
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
 
LVL 8

Expert Comment

by:drittich
ID: 7099077
>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
 

Author Comment

by:eugene007
ID: 7099986
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 8

Expert Comment

by:drittich
ID: 7099993
Well, you know the order your are doing things in, so do what seems right!
0
 

Author Comment

by:eugene007
ID: 7100253
he he ok.
0
 

Author Comment

by:eugene007
ID: 7100263
he he ok.
0
 

Author Comment

by:eugene007
ID: 7100353
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
 
LVL 8

Expert Comment

by:drittich
ID: 7100530
that is off-topic, time for a new question
0
 

Author Comment

by:eugene007
ID: 7100598
well would anyone care to give me 100 points free :)
0
 

Author Comment

by:eugene007
ID: 7115587
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
 
LVL 8

Expert Comment

by:drittich
ID: 7117412
I am not sure that this question will ever end...
0
 

Author Comment

by:eugene007
ID: 7117770
every beginning has an end to it.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7265894

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
 
LVL 8

Expert Comment

by:drittich
ID: 7265983
I feel I should get the points on this one.  I answered several questions here.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7266022
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
 
LVL 8

Expert Comment

by:drittich
ID: 7266328
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7266773
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
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7296605
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
 
LVL 5

Expert Comment

by:Netminder
ID: 7317804
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now