CODEWITHSUNDEEP
mysqldatabase-design
William82
William82

Reputation: 89

how should I build up my database when I want to store these kind of data?

I want to build a page like shown below and all data should be retrieved from a database. Both the term, subject and sentences is retrieved from a database. Three levels of data. And under each term (eg. Spring 2017) I can pick and choose between all of these sentences.

Spring 2017

Subject1

Subject2

Subject3

Autmn 2017

...

I want to present similar info from database to user, and let the user choose between all this sentences. How should i build up my database for achieving this in the best and most efficient way.

One way is:

Table 'subject'           Table 'sentences'
|  id  |  subjects  |     |  id  |  subjectid |      name     |
|   3  |  Subject1  |     |  1   |      3     |   Sentence 2  |
|   4  |  Subject2  |     |  2   |      4     |   Sentence 13 |

Table 'term'
|  id  |        term       |      sentenceid     |
|  1   |      Spring 17    |        1,2,28       |

Another way is maybe using pivot-tables, something like this:

     Table 'sentences'
     |  id  |  parentid  |      name     |
     |  1   |      0     |   Subject2    |
     |  2   |      3     |   Sentence 2  |
     |  3   |      0     |   Subject1    |
     |  4   |      1     |   Sentence 13 |

Table 'term'
|  id  |        term       |      sentenceid     |
|  1   |      Spring 17    |        2,4,28       |

Notice: Number of terms can be many more than just two in a year. Is it any of this structures you recommend, or any other way you think I should build my database? Is one of these more efficient? Not so demanding? Easier to adjust?

Upvotes: 2

Views: 87

Answers (4)

AnoE
AnoE

Reputation: 8355

You are doing relational analysis/design:

  1. Find all substantives/nouns of your domain. These are candidates for tables.
  2. Find any relationships/associations between those substantives. "Has", "consists of", "belongs to", "depends on" and so on. Divide them into 1:1, 1:n, n:m associations.
    • look hard at the 1:1 ones and check if you can reduce two of your original tables into one.
    • the 1:n lead you to foreign keys in one of the tables.
    • the n:m give you additional association tables, possibly with their own attributes.

That's about it. I would strongly advise against optimizing for speed or space at this point. Any modem RDBMS will be totally indifferent against the number of rows you are likely to encounter in your example. All database related software (ORMs etc.) expect such a clean model. Packing ids into comma separated fields is an absolutes no-no as it defeats all mechanisms your RDBMS has to deal with such data; it makes the application harder to program; it confuses GUIs and so on.

Making weird choices in your table setup so they deviate from a clean model of your domain is the #1 cause of trouble along the way. You can optimize for performance later, if and when you actually get into trouble. Except for extreme cases (huge data sets or throughput), such optimisation primarily takes place inside the RDBMS (indexes, storage parameters, buffer management etc.) or by optimizing your queries, not by changing the tables.

Upvotes: 3

user6232480
user6232480

Reputation: 31

This table design Should resolve your need. 

TblSeason
  (
  SeasonId int,
  SeasonName varchar(30)
  )

  tblSubject
  (
  Subjectid int
  sessionid int (fk to tblsession)
  SubjectData varchar(max)
  )

  tblSentences
  (
  SentencesID INT
  Subjectid int (Fk to tblSubject)
  SentenceData varchar(max)
  )

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Start with the terms. Every term has subjects. Every subject has sentences. Then you may need the position of a subject within a term and probably the position of a sentence in a subject.

Table 'term'

id | term
---+------------
1  | Spring 2017


Table 'subject'

id | title    | termid | pos
---+----------+--------+----
3  | Subject1 | 1      | 1
4  | Subject2 | 1      | 2
5  | Subject3 | 1      | 3


Table 'sentence'

id | name        | subjectid | pos
---+-------------+-----------+-----
1  | Sentence 2  | 3         | 2
2  | Sentence 13 | 4         | 1
3  | Sentence 1  | 3         | 1
4  | Sentence 3  | 3         | 3
2  | Sentence 17 | 4         | 3
...

Upvotes: 0

JosephStyons
JosephStyons

Reputation: 58795

If the data is hierarchical, consider representing it with a single table, with one column referencing a simple lookup for the "entry type".

Table AcademicEntry
================================
| ID | EntryTypeID | ParentAcademicEntryID | Description |
==========================================================
|  1 |    3        | 3                     | Sentence 1  |
|  2 |    1        | <null>                | Spring 2017 |
|  3 |    2        | 2                     | Subject1    |

Table EntryType
================================
| ID | Description |
====================
|  1 | Semester    |
|  2 | Subject     |
|  3 | Sentence    |

Upvotes: 1

Related Questions