MySQL multiple TABLE entries (database design)

Discussion in 'Databases' started by koolsamule, Feb 5, 2010.

  1. #1
    OK chaps,

    Currently, I have a database with a (translation) projects table, and 3 jobsheet tables linked to the projects table by a foreign key. Each jobsheet can only have 1 'target' language, along with 'wordsgross'.

    What I'm after is a 4th table which can contain multiple 'target' languages, along with corresponding 'wordsgross', something like this:

    //TABLE:
    jobid (int)
    jobname (varchar)
    FK_projid (int)
    FK_langid (int)
    wordsgross (int)

    The FK_langid and wordsgross are the columns that need to be linked together, then repeated for each 'target' language.

    I'm not sure if this makes sense, the only other thing I can think of is a Car Insurance website when you need to 'Add Another Driver', then another, and another . . . .these multiple entries are still linked to same account, but allow 'unlimited' drivers (just like I need 'unlimited' languages).

    If my understanding of how to do this is completely wrong, please set me straight.

    Cheers
     
    koolsamule, Feb 5, 2010 IP
  2. RonBrown

    RonBrown Well-Known Member

    Messages:
    934
    Likes Received:
    55
    Best Answers:
    4
    Trophy Points:
    105
    #2
    The table you have displayed should not have the "wordsgross" on it as it is unecessary - I'm assuming that "wordsgross" are a list of words associated with a particular language.

    Create 1 table containing a list of languages

    e.g.

    languageID
    langauge
    any other specific information

    another table with a list of "wordsgross".

    e.g.

    wordsgrossID
    wordsgross_word

    The reason I'm suggesting this is that "wordsgross" may belong to more than 1 language so it is better to have just one table of these words.

    Then create another table where the languageID and WordsgrossID are linked

    e.g.

    languageID
    wordsgrossID

    make both the languageID and wordgrossID compulsory and set them up as a primary-key pair so you don't get repetition.

    Now, when you create a new job, just by associating the language with the job, it will also be able to associate all the "wordsgross" with that job at the same time.

    It seems like a lot of tables but this granularity will make it easier to manage in the future. If you are used to using things like Views in your DB then this will be even easier to manage and you'll be able to extract all the necessary information in a single database connection.

    You can also set constraints on the system so that whenever a new wordsgross word is added it must also added to the linking table (the languageID, wordsgrossID) so that the wordsggross is ALWAYS associated with at least one language (keep your tables and relationships well formed).


    I may have misunderstood this completely and if I have, my apologies.
     
    RonBrown, Feb 5, 2010 IP