5

This is a database schema I propose for a networking website like the Classmates.com web site.

Do you think the database has all the tables needed for such a project? What would you rather do?

The database has 3 parts:

  • the payment part is surrounded by the black color box
  • the social part by the blue color box
  • the classmates part by the red color box

Any advice or critics is welcome. thanks.

I have been inspired by database models presented on the databaseanswers.org web site.

enter image description here

enter image description here

Improve this question
3
  • 1
    Just personal preferences, but to me your schema lacks the ability to keep track of name changes due to marriage, etc, timestamps of the name changes, password lockout counts, last logon times, physical addresses for schools, display names in the Contacts table, video name, profile photo titles/descriptions, blah blah blah.
    – beeks
    Jan 6 '16 at 17:25
  • Thanks Beeks for comments. About the ability to keep track of name changes (timestamps of the name changes, password lockout counts, last logon times) do I have to add those new fields into the Users table?
    – cProg
    Jan 7 '16 at 13:43
  • 2
    i would break out the users table into a Users table and UserProperties table, joined by the PK Users_ID. The Users table would store information about the user that we don't need to keep a historical record of, like: Users_ID User_Create_Time Last_Logon_Date Password_Hash Failed_Logon_Attempts User_DOB etc.. Then on the UserProperties table Users_ID Property_ID EffectiveStartDate Property_Type Property_Value Then you can join the User to the property to the date and the time, to find the state of a user's information at any point in time.
    – beeks
    Jan 7 '16 at 15:42
1

1.   payment (black box)

1.1)  No currency information?
In the Payments table, although you store an amount and date, you store no currency. Perhaps you plan on only charging in one currency, but I feel that method will only last so long if this is intended to scale.

1.2)  What is this second payments table?
What is the second payments table for? The one below and slightly left of the actual payments table? It seems to have only an id column, which is the only column in this diagram to specify a data type!? I'm guessing this is some diagram artefact.

1.3)  You'll need more than just the name to take payments!
The payment_methods table currently doesn't store the type (i.e. credit card, paypal, BitCoin, whatever) of the method. There also seems to be no linked table of payment_types for this either.

You will need to store stuff like credit card numbers, etc somewhere (hopefully not in this database!). You will need some sort of linking information for those stored payment methods to be kept in the payment_methods table.

2.   social (blue box)

2.1)  Who exactly is me?
I'm assuming the Me table is some sort of façade or profile type of table. Perhaps allowing a given User to shield themselves from the embarrassment of letting their collage/university friends see the Music they were into in high school? :P

I do not believe it makes sense to double store the first, middle and last names of the user. It would be much better to go with a solution like that which @beeks suggested in the comments above.

2.2)  My_Profile_Details... field?
I cannot figure out why this is a single field. I assume it's some sort of memo type, for storing a lot of text (i.e. a CV of sorts). I think this needs MUCH more decomposition. At a minimum, into Sections of some sort.

There also seems to be nothing in a Proflie that allows a user to select what Schools/Collages/Universities/Enterprises they studied/worked at, etc?

3.   classmates (red box)

3.1)  Internationalisation?
This part of your design is missing information regarding country. Even if you're only looking for schools/colleges in a particular county, employers (which is what I assume Enterprise is for) may be in any country.

Also, If a classmate starts their own enterprise, do they have the same relationship to that enterprise as someone who works for it?


3.2)  Institution Names
You have assumed Universities (and I suppose high schools, primary schools and collages too) have only one name, and that name is in English (or whatever language you're intending to store). It is quite common for universities to have different names in different languages. Institution names can also change over time (renamed, etc).


3.3) Institution Decomposition
Not all Universities divide themselves into Faculties, some are simply a collection of Schools (think Oxford, etc). These can also change name over time. Also, both faculties (schools), universities (and probably collages, primary and secondary schools too) can merge (i.e. get taken over/bought out) over time.


3.4) Institution Location Multiplicity
Your design seems to assume a University can only be in one town. Many Universities have different campuses in different towns. Some even have campuses across international borders.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.