1

I am trying to model the situation where someone can belong to multiple industries and have different years of experience in each industry. They can also know several languages and have different amounts of experience with each language.

What makes sense to me are 2 one-to-many relationships, which I hope I am conveying with the tables below. A profile can have many industries and languages.

Profile
id pk
Industry
profile fk-> Profile.pk
name varchar
years int
Language
profile fk-> Profile.pk
name varchar
years int

However, would it make more sense to model it with an "experience" join table like below?

Profile
id pk
Experience
industry Fk-> Industry.pk
language Fk-> Language.pk
years int
Industry
id pk
name varchar
Language
id pk
name varchar

To me, the first way seems simpler, and I am having a tough time understanding why the second way would be preferred, but I don't know why in either case.

Improve this question
2
  • 2
    Welcome to the site, in the future it's better to actually speak to the site in SQL (with the actual create statement commands) rather than in a display table. But you'll get the hang of it. ;)
    – Evan Carroll
    Commented Jan 6, 2022 at 3:58
  • 1
    Thank you! I will make sure to do that in the future!
    – user3162539
    Commented Jan 6, 2022 at 4:06

2 Answers 2

Reset to default
1

If I understand correctly what you're trying to model, both are wrong.

As I understand it, you have three entities: Profiles, industries and languages. Profiles are related, many to many, to industries, with an relationship attribute of a duration (in years, if you will) this relationship has been established and analog (but separately!) for languages.

In your first model, you have to repeat the industry's (or language's) name and (maybe other attributes of industries (languages)) in every relation to them to a profile and you have the names (or other attributes) only there. That follows,

  • if you want to change a name (or other attributes), say to correct a typo, you have to change it in all links to a profile, if you forget one (some), you'll have inconsistent data
  • and if you delete the last link to an industry (language), the industry (language) would cease to exist in your database.

Your second model corrects that by having a table for the industries and the languages each. Your linking table points the right was but is flawed as it tries to somehow squeeze two relationships into one. To which of them the duration belongs? To the relation with an industry or with a language?

Having three entities you need three tables, one for each.

CREATE TABLE profile
             (id serial,
              name text,
              -- other attributes
              PRIMARY KEY (id));

CREATE TABLE industry
             (id serial,
              name text,
              -- other attributes
              PRIMARY KEY (id));

CREATE TABLE language
             (id serial,
              name text,
              -- other attributes
              PRIMARY KEY (id));

Then, as the profiles relate to industries and languages, which adds up to two relationships, you need two linking tables with a column for the duration each, to model the relationship attribute.

CREATE TABLE profile_industry
             (profile integer,
              industry integer,
              duration integer, -- or you might even want to use the interval type to be extremely precise and flexible
              -- other attributes
              PRIMARY KEY (profile,
                           industry),
              FOREIGN KEY (profile)
                          REFERENCES profile
                                     (id),
              FOREIGN KEY (industry)
                          REFERENCES industry
                                     (id));

CREATE TABLE profile_language
             (profile integer,
              language integer,
              duration integer, -- or you might even want to use the interval type to be extremely precise and flexible
              -- other attributes
              PRIMARY KEY (profile,
                           language),
              FOREIGN KEY (profile)
                          REFERENCES profile
                                     (id),
              FOREIGN KEY (language)
                          REFERENCES language
                                     (id));

Or instead of a duration you can also change or add an attribute to the relation with a language to something more menaingful, like an ACTFL proficiency level for example (best define and use an enum type for that).

Improve this answer
9
  • Thank you very much! I now see why the name change of an industry (language) could be problematic, as well as the removal of the last industry (language). I think where I will have to do further learning is understanding when a one-to-many relationship makes more sense.
    – user3162539
    Commented Jan 6, 2022 at 3:42
  • while id serial is short, it's horrible form. you should use identity columns. You also don't need to say REFERENCES profile (id) if you're linking to the primary key you can just write REFERENCES profile. As a general habit too, your better off if your surrogate key's aren't all id but have the table name profile_id
    – Evan Carroll
    Commented Jan 6, 2022 at 3:55
  • @EvanCarroll: OK, but honestly, I don't know the identity syntax by heart and was (and am) too lazy to look it up for a mere sketch. I disagree on the other points though. I prefer to be verbose in foreign keys and find it useless to add _id prefixes to referencing columns. I know that it is a reference by looking up the definition, same as I have to lookup a column's the datatype to know what (sort of) type of data goes in there, the name should be strictly semantically. And those suffixes are a mess on composite keys anyway. But well, that's just some different opinions, I guess...
    – sticky bit
    Commented Jan 6, 2022 at 4:08
  • I'm not just stating that you should add _id to just referencing columns. I am stating that both the surrogate id and all referencing column should follow the format of table_id the reason why is because if you do that, you'll never have confusion in a join, and you can use USING (and if you want at least for development SELECT *). For example, SELECT * FROM (VALUES (1)) AS g(g_id) JOIN (VALUES (2,1)) AS k(k_id,g_id) USING (g_id); There is slight pressure from the language to write your tables like that, and it's considered good practice.
    – Evan Carroll
    Commented Jan 6, 2022 at 4:26
  • 1
    @EvanCarroll: Well, it looks silly to me if I only prefix the id column but not the others. And if I do it to all it becomes ridiculously convoluted and pointless to me (and why would I not start to add the schema as a prefix as well, etc.). And I never use USING and and also advise not to use it. I prefer to be clear and verbose and therefore I prefer to use table prefixes in queries with multiple tables anyway. But again, that's my opinion, you may have yours, neither is wrong or right. ;)
    – sticky bit
    Commented Jan 6, 2022 at 4:32
0

A Experience table isn't a good idea. You'll frequently get into trouble trying to use a table for two different purposes.

So recommend something like the following with the implication that Profile is a person.

Profile
id pk
IndustryExperience
profile fk-> Profile.pk
industry Fk-> Industry.pk
years int
LanguageExperience
profile fk-> Profile.pk
language Fk-> Language.pk
years int
Industry
id pk
name varchar
Language
id pk
name varchar
Improve this answer
1
  • Thank you very much - I really appreciate the table recommendation!
    – user3162539
    Commented Jan 6, 2022 at 3:36

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.