0

I am a beginner with data modelling and I am trying to construct a logical data model using Oracle SQL Developer Data Modeler.

I am not sure when to and when not to use sub-types and how should they be represented.

For example, if I have an entity "STUDENT" and an entity "COURSE" which can be something like "BACHELOR OF ARTS" or "MASTERS OF IT" etc. and COURSE_TYPE as "BACHELORS" or "MASTERS" but not both.

NOTE: MASTERS type courses will have other entities related to it (ONLY) which is irrelevant to BACHELORS. So it will help to have a MASTERS entity box separately to make the relevant connections.

How should I model this ?

Options:

  1. Have "COURSE_TYPE" as an entity (which will have "COURSE_TYPE_ID" and "COURSE_TYPE_DESC") and "COURSE_COURSE_TYPE" as another entity (which will have attributes COURSE_ID, COURSE_TYPE_ID)and connect both "COURSE" and "COURSE_TYPE" to it?

  2. Have "COURSE_TYPE_ID" as an attribute in "COURSE" and have another entity "COURSE_TYPE" which will simply have COURSE_TYPE_ID and "COURSE_TYPE_DESC" as attributes ?

  3. Subtype the entity "COURSE" using XOR relationship as "BACHELORS" and MASTERS"

  4. Subtype the entity "COURSE_TYPE" using XOR relationship as "BACHELORS" and MASTERS"

  5. Have an entity "COURSE" and add 2 entities within it for "BACHELORS" and MASTERS" (box in box)

learner
  • 833
  • 3
  • 13
  • 24
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Oct 16 '20 at 06:53
  • This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Oct 16 '20 at 06:55
  • Others cannot tell anyone what is "best" in an engineering situation unless they define it in enough detail & give enough relevant details that everyone would agree on a valuation. And unfortunately results are chaotically dependent on details. And even if that could be done it's too much for a question here. And you would need to explain where & why you were stuck evaluating that yourself. Such questions are too broad. Although nevertheless often faqs. [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461/266284) – philipxy Oct 16 '20 at 06:55
  • Thanks @philipxy I am honestly not sure how to ask questions. If I ask questions such as "How to represent inheritance in a DB", I am told it's too broad and if I qualify my question with examples and list options ( I am sure couple of them are wrong, I want to know what is right not what is best) and be specific, even then I am told there isn't enough detail. How is my question broader than "how to represent inheritance in a database" ? I am not sure why, but this is probably the 3rd or 4th question of mine you have voted to close. Thanks anyways. I guess I have to look for help elsewhere :-( – learner Oct 16 '20 at 07:08
  • A standard comment of mine: 'There's no such thing as "better"/"best" in engineering unless *you* define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question.' To ask if a basic design is wrong headed, give it & your reasoning & research including testing. – philipxy Oct 16 '20 at 07:46
  • Q&A is a poor way to learn. It is good for getting unstuck. You need experience. Your Q isn't broader than the duplicate, the duplicate is too broad itself, but it might give more options & hints on pros & cons. Not all questions fit SO. Here's what I often comment: 'Follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) Ask 1 specific researched non-duplicate question where stuck.' PS [dba.se] [softwareengineering.se] – philipxy Oct 16 '20 at 07:52
  • Also re "best" here's another standard comment: re performance aspects: 'Learn the basics of relational & SQL optimization/implementation/performance by query engines in general via textbooks & the web & SO & in particular via DBMS manual--all immediately leading to indexes, plans, statistics & SARGability. Ask re optimization after you have learned & applied those basics. Appropriate research is expected for questions.' Qs asking for As that are tutorials & books/chapters are just too broad. And get unhelpful answers that are random fragments of what an informed person thought while solving. – philipxy Oct 16 '20 at 08:03

0 Answers0