0

I was looking for some advice on a data model that I came up with. The idea that I have is to do a "Word of the day" app. I have the following tables:

Words, Word Categories, Categories, Definition, Grammar

Because of the fact that words can have many categories and categories can have many words, I've added the Word_categories table. Definition exists separately from words, because a word can have multiple definitions. Grammar connects into definition, as a word's grammatical use would be determined by its definition. Is there anything that I'm missing or have wrong here?

I get the feeling that the primary key, for the Word_categories is unnecessary, but it came recommended within Vertabelo. Data Model Draft

ParanoidPenguin
  • 151
  • 1
  • 13
  • Instructions on using a tool for recording designs are not instructions on how to do design. Find an information modeling & database design textbook/reference, follow it, and ask when you have a specific problem. Here your description of your cardinalites is more or less reflected 1:1 with your DDL, OK. But good designs are not based merely on that, so even for us to tell you what info you should give and whether you've designed for it is to rewrite such a textbook/reference. Re Word_categories, google 'vertabelo' with 'composite primary key', also with 'alternate key' & 'candidate key'. – philipxy Dec 09 '17 at 05:57
  • I am working off a book, not the instructions within the Vertabelo site. That being said, I think you are right that the question lacks specificity and is probably not well suited towards this platform. I had some specific questions. For instance, does it make sense to denormalize the ‘Word_categories table’? It has been suggested to me that, with growth in the number of words and a potential for large numbers of category associations, that the table could impact performance. Should I close down the post and ask the specific questions separately? – ParanoidPenguin Dec 09 '17 at 12:27
  • It's just that "is this right" is too broad/unclear/opinion-based. (See close vote reasons.) Notice two specific questions I mentioned that you could ask about--reflecting cardinalities & avoiding surrogates. In engineering if you can't define 'right', 'best', etc *yourself* in enough detail that everyone could apply it and get the same answer (including you--so why ask?) then you're just using a word without knowing what it means, you're not actulally saying anything, you are hoping others will apply magic knowledge--you are implictly presuming the answer is too general for SO. – philipxy Dec 09 '17 at 23:04
  • ... Even asking re "denormalize the ‘Word_categories table’" is misconceived since the only way to denormalize it is (yuk) replace it by its projections {id, word_id} & {id, categories_id}. It seems unlikely that you mean that, or that you understand what "denormalize" means, or "normalize". I don't know what you do mean because it is basic here to have a table {word_id, categories_id} & also that you don't need an extra id, and it's not even clear what you could intend to replace it by. But telling us & asking re such a specific point is good--but it's a faq (like the 2 specific ones above). – philipxy Dec 09 '17 at 23:15
  • PS I suggested the google re 'composite key' so you could learn about how to not have the Word_categories surrogate id if you don't want it--that's a specific question. But it's easily answered by that google, although I expect it's also a SO faq from people not researching. See [ask] & other [help] links including re upvoting, downvoting & close voting. Re this question, edit to be more specific and/or wait for others' input. You'll likely get an answer. (Whether or not you should, or it's helpful.) Google my comments re 'best OR efficient OR optimized'. – philipxy Dec 09 '17 at 23:31
  • @philipxy The point about 'denormalizing' was discussing a performance issue of having the number of records grow quickly within the joining tables. They advocated that I instead have 'category ids' within the words table, separated by CSV. Now, this to me is a violation of 1NF, but lack of experience on my part makes it difficult to understand if there are justifiable cases to do this or not. Thanks for taking the time to respond, it's appreciated. – ParanoidPenguin Dec 10 '17 at 22:15
  • Zillions of (duplicate!) SO answers await you if you would force yourself for each specific inquiry/goal/problem/desiderata to google many clear, concise & specific phrasings of it & read many hits each time. Eg 'site:stackoverflow.com why should i not have multi valued attributes database'. Normalization to 1NF happens to be a historical/cultural boondoggle so read [this by me & all its network of links to my answers](https://stackoverflow.com/a/40640962/3404097) (including re how all attribute values in a relational DB should be ones you don't want to query about the parts of. Good luck. – philipxy Dec 11 '17 at 04:08

0 Answers0