First of all, some tables are seperated but really hold the same kind of data.
For example, video, audio and images are all saved as urls, so you can join them in one table instead of 3.
This will greatly simplify your schema, and allow you to permit several types of content in one collection (for example, a wedding album that contains images & video).
Second, if users upload the content, how come you can keep it in urls? Where is the data actually saved? My first thought when seeing this was that you need to keep a binary object column for the content itself.
Third, did you mean to have a many-to-many connection between content and albums? It means every item can be in several albums. If you would like to limit an item to only one album, you would not use a "connector" table, but instead keep an album id in the content table itself (a foreign-key to the album table).
And last - as a "social media file sharing" schema, it seems to be missing the social aspect (where is sharing information saved?).
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.
Best Answer
There are some details of your application that could change this model, but hopefully it's enough to get started.
Defining an Interviews entity is useful because you can store data that is associated with the Interview itself, for example when the interview happened and who the interviewer was.
There are three types of Interviews here: TextInterviews, VideoInterviews, and AudioInterviews. Since the question-answer pairs only apply to TextInterviews (assumption), the foreign key is to this table.
Each question has up to one answer (assumption). Because of this, the Questions and Answers could be combined into a QuestionAnswers table, although it doesn't have to.