0

What it is

Have many stories (with many lines in each). Each translated in many languages. Each language have many accents. I'd want to offer user to choose the languages (can choose many of only which has all stories covered) then choose exactly one accent from all the accents of each language.

Data example

Suppose we've 2 stories, each translated in 3 languages and each language has 5 accents (audio vocal)

Data access requirement

Now think that user chosen
Language 2 with accent 1 and Language 3 with accent 4

Now audio will be played in like following

  1. Story 1 > Line 1 from Language 2 of Accent 1 then Story 1 > Line 1 from Language 3 of Accent 4
  2. Story 1 > Line 2 from Language 2 of Accent 1 then `Story 1 > Line 2 from Language 3 of Accent 4
  3. // continues till the end of the story & then starts new story & play like this

Possible entities/ tables

  1. Stories
  2. Lines
  3. Languages
  4. Accents
  5. Audios

Obviously I made up this, Need help here.

Where the help is needed

To fulfill my intended use of the data, how the tables should look like, what'd be the relations between them (like stories hasMany Languages, things like that).
And ...
How can I query data to offer user about the 1st selection in "What it is" section in top.

About me: I'm experienced in software development but not that much in complex schema design. I (think) can handle fairly complex DB stuff but obviously not this.

Info: I'm doing it in MySQL (MariaDB)

15
  • 1
    You state in the title that you need help, but from the question it appears you want the entire design done for you; this is unlikely to happen. Perhaps if you give it a shot yourself, show us your ER diagram, and ask a specific question about it, someone could answer that.
    – mustaccio
    Commented Feb 12 at 21:00
  • MariaDB is not MySQL. MariaDB started as a fork of MySQL in 2010, but both product have been changing since then, not always in compatible ways. Now we should think of them as totally different products, and answers for one are not necessarily going to work on the other. Commented Feb 12 at 21:47
  • @BillKarwin does the difference between the two products affect the logical datagase design though?
    – mustaccio
    Commented Feb 12 at 23:02
  • @mustaccio To the extent that MariaDB and MySQL don't support exactly the same queries, it might affect the design. I'm just trying to remind developers they they shouldn't say they're using "MySQL (MariaDB)" just as they wouldn't say they're using "Sybase (PostgreSQL)". One is not a type of the other. They are different products. Commented Feb 12 at 23:33
  • 1
    Schema design is an iterative (and links within) process. Design schema_1, code against it, test, find bugs (and there will be bugs), then design schema_2 and rinse and repeat until your system performs to your satisfaction! You can't expect to have a complete correct schema handed to you on a plate on a forum such as this! Ask specific questions about a well-defined schema rather than asking a vague question about how to design a system!
    – Vérace
    Commented Feb 29 at 3:44

2 Answers 2

1

Looking at your diagram, I think your difficulty starts where you mistakenly try to model "Languages" as a subordinate of "Lines", when it is actually an independent entity. Apart from this, the rest of the model seems fine at the conceptual level.

Taking keys into account, there is the common issue with putting an Id column as a primary key everywhere. At least you did not use yet another id for "Audios".

After fixing the first issue and attempting a more logical use of surrogates in order to compose relational keys, I think you can use a model similar to this:

enter image description here

For your next question, I am not really sure what you are trying to query, but it will be easy enough. For example, you can use something like this to get the narration progress of a story in a specific language accent:

CREATE VIEW Narration_V
AS
SELECT Story.Name          AS Story,
       Language.Name       AS Language,
       LanguageAccent.Name AS Accent,
       (
           SELECT COUNT(*)
           FROM StoryLineRecording
           WHERE StoryNo = Story.StoryNo
             AND LanguageCode = LanguageAccent.LanguageCode
             AND AccentNo = LanguageAccent.AccentNo
       ) * 1.0 /
       (
           SELECT COUNT(*)
           FROM StoryLine
           WHERE StoryNo = Story.StoryNo
       ) AS Progress
FROM Language
INNER JOIN LanguageAccent
ON Language.LanguageCode = LanguageAccent.LanguageCode
CROSS JOIN Story;

After a second read I think I better understand your query requirements. To get a list of those accents where every story is fully narrated into, you can use something like this:

SELECT Language.Name AS Language, LanguageAccent.Name AS Accent
FROM Language
INNER JOIN LanguageAccent
ON Language.LanguageCode = LanguageAccent.LanguageCode
WHERE NOT EXISTS
          (
              SELECT 1
              FROM Story
              WHERE EXISTS
                        (
                            SELECT 1
                            FROM StoryLine
                            WHERE StoryNo = Story.StoryNo
                              AND NOT EXISTS
                                (
                                    SELECT 1
                                    FROM StoryLineRecording
                                    WHERE StoryNo = Story.StoryNo
                                      AND [LineNo] = StoryLine.[LineNo]
                                      AND LanguageCode = LanguageAccent.LanguageCode
                                      AND AccentNo = LanguageAccent.AccentNo
                                )
                        )
          );

This is the best I can come up with right now but there may be a simpler solution.

1
  • Thank you very much, I really appreciate your help 👍 :). This helps a lot
    – Md. A. Apu
    Commented Feb 18 at 8:08
1

In my opinion, your task is simple enough to write code that works 'identically' in both MariaDB and MySQL.

There are two "things" to think about:

  • Entity -- Implement as a table with information about lots of one type of thing (eg, Stories)
  • Relations -- Implement via FORIGN KEYs and JOINs. And possibly via a "many-to-many" mapping table.

Let me discuss three schema patterns:

  • one-to-many -- Where the "many" table has an id linking to the "one" table.
  • many-to-many -- Where there is an extra table that has two ids linking to each of two tables.
  • "mapping" or "normalization" table -- This is a table whose main purpose is to map between a short id and a longer string.
  • (I am leaving out one-to-one because they should usually be merged into a single table.)

Let me take your example, stories hasMany Languages, where the table Stories has general information about stories. (This is an "Entity".)

Another table,Stories_Languages, has story_id and a column (lang_id) with "en_US", "en_GB", "fr_FR", "fr_CA, etc. (This is a "Relationship".)

If that is all you need for language/accent, then Stories is a simple "one-to-many" table and Stories_Languages is a simple mapping table.

However, if there are lots of details about the language, you need a separate Entity table, Languages and Stories_Languages becomes a full-blown "many-to-many" table.

4
  • Thank you very much for the answer, Unfortunately I need bit more details, Here is a diagram I've created but it's not complete as per my requirement. The DB can have n Stories and Languages, Here stories can be in many different languages, where each language of a story can have multiple accent, Finally that accent has audio file that can be played by the user. This is a story telling app. it has many stories, each story has translated in many different languages, each language has many different voices (accents), How to...
    – Md. A. Apu
    Commented Feb 14 at 7:18
  • - Design the tables & their relations for this scenario? You're free to change the diagram I've created :)
    – Md. A. Apu
    Commented Feb 14 at 7:20
  • @Md.A.Apu - Is "accent" merely a variant on a "language"? If so, I would combine them into a single Entity. (I prefer to work form CREATE TABLEs, and SELECTs. Maybe I will advise some more when you get those written.)
    – Rick James
    Commented Feb 14 at 19:06
  • Thanks @Rick As a language can have multiple accents (voices), thus I like to separate that entity. I just need your help of defining the relations between those entities/ tables in the diagram I've shared in the first comment. Please also correct me if i'm wrong in any point
    – Md. A. Apu
    Commented Feb 15 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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