1

I am designing a database on a logical level to later pass it on for programmers to deliver. I am only superficially familiar with how they work, so I am struggling to phrase my problem succintly. Here's my problem:

I have a table named MEANINGS.

I have a table named WORDS.

I have relational table named M-to-W.

I have a table named GRAMMAR.

Words can have multiple meanings, meanings can have multiple words. But I also wanna add grammar. I could just add a row in WORDS, but sometimes a word behaves differently depending on meanings (like the past tense is “He hanged himself” for suicide and “He hung a picture on the wall” for putting). Hence, I see two solutions on a logical level:

  1. GRAMMAR could be linked with the M-to-W relational table with another relational table, referencing entire relationships (certain meanings of certain words have specified forms).
  2. GRAMMAR items could be referenced by M-to-W in additional row.

I have only created relational databases with Django/MariaDB (pretty much automatically), so I don't know if these can have more than two columns. I don't know if referencing pairs of properties is possible.

Is any of these solutions possible, or is there a better practice to handle this kind of structural problem? Is using composite keys easier? Please assume keeping words and meanings in separate tables is necessary.

MrVocabulary
  • 597
  • 1
  • 11
  • 31
  • 1
    Rather call `M-to-W` a relationship table than a relational table. If you have a first-order logical model, all of your tables are relational tables. Think of relation as a synonym for table, but with some extra rules, like no duplicate rows/columns and order of rows/columns don't matter. – reaanb Oct 15 '17 at 09:41
  • Okay, thanks, didn't know that! – MrVocabulary Oct 15 '17 at 09:52
  • 1
    In the relational model, relations (tables) represent n-ary relation(ship)s. You need to read some book(s) on info modeling, the relational model & DB design. [Here are some I can endorse.](https://stackoverflow.com/a/24007275/3404097) Re whether you can or should replace a ternary relation(ship) by others falls under "dependency theory" which involves "normalization to higher NFs (normal forms)". – philipxy Oct 16 '17 at 09:49
  • @philipxy thanks, will now proceed to reading them! – MrVocabulary Oct 16 '17 at 10:31
  • 1
    Possible duplicate of [Composite Primary key vs additional "ID" column?](https://stackoverflow.com/questions/14588304/composite-primary-key-vs-additional-id-column) – philipxy Oct 17 '17 at 00:17
  • 1
    PS See [this answer, its PS 3 & its question](https://stackoverflow.com/a/45557994/3404097) re binaryrelation(ship)/table on an associative entity & a non-associattive entity vs ternary in ER modeling and re binary instead of ternary via id PKs/FKs. – philipxy Oct 17 '17 at 00:21
  • The questions you referred me to were very instructive, but I think that my question is more about weighing two possibilities against each other. – MrVocabulary Oct 17 '17 at 13:24

1 Answers1

2

Before defining the schema, decide what data you have, and how it will be put into the database.

And decide what you want out of the database.

Give the programmers that info, not the specifications of the tables. Let them figure out the best way to structure the schema.

If none of them have any database background, then there is a learning curve. Answers from this forum will only help you out of one pitfall after another. Please take the time to get at least one programmer trained.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1
    Thanks for the advice. I know what data I have and how it will be put there. But I don't have any programmers yet; I am preparing a specification. Is solution to that problem indeed so specfic case-dependent? – MrVocabulary Oct 14 '17 at 17:50
  • 2
    It _may_ be better to have a single table for everything, rather than a 3-headed many-to-many mapping table. Can't tell without trying it. (I don't know if Django has a restriction.) – Rick James Oct 14 '17 at 18:07
  • Django is not an absolute given here, but while words & grammar can be groupped up, meanings _must_ be separate. – MrVocabulary Oct 14 '17 at 18:11