Need help on database design for 5 entities complex relations
-1
votes
2
answers
155
views
### 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.
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)
**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)
Asked by Md. A. Apu
(123 rep)
Feb 12, 2024, 08:28 PM
Last activity: Feb 17, 2024, 09:49 AM
Last activity: Feb 17, 2024, 09:49 AM