Professional Web Applications Themes

Two MySQL database moddeling questions... - MySQL

Hi all, This may seem simple but its really doing my nut in. Can somebody please confirm and answer my questions else I will keep going round in circles... TIA :) 1) Am I able to create an entity (table) with two foreign keys as the primary key? i.e. say I have a 'location_source' entity with 'location_id' (PK) and 'map_id' (PFK). Is it good practice to create a new entity with these two keys as an identifying relationship. Say my new entity was called 'location_info' and it has 'location_id' (PFK) and 'map_id' (PFK) making up the primary key but no ...

  1. #1

    Default Two MySQL database moddeling questions...

    Hi all,

    This may seem simple but its really doing my nut in. Can somebody
    please confirm and answer my questions else I will keep going round in
    circles... TIA :)

    1) Am I able to create an entity (table) with two foreign keys as the
    primary key? i.e. say I have a 'location_source' entity with
    'location_id' (PK) and 'map_id' (PFK). Is it good practice to create a
    new entity with these two keys as an identifying relationship. Say my
    new entity was called 'location_info' and it has 'location_id' (PFK)
    and 'map_id' (PFK) making up the primary key but no PK identifier of
    it's own?

    2) I need the best data model for the following :

    Entitys...

    a) entity 'game' with 'game_id' (PK)
    b) entity 'map' with 'map_id' (PK)
    c) entity 'location' with 'locaiton_id' (PK)
    d) entity 'location_type' with 'location_type_id' (PK)

    Relation Rules...

    A 'game' can have only one 'map' related to it.
    A 'map' can be related to many 'games'.

    A 'map' can have a number of 'locations' related to it.
    'Locations' can only be related to 1 'map'.

    A 'location' can have only one 'location type'.
    A 'location type' can be related to many 'locations' but can only be
    used for the 'map' that the 'location' is related to.

    Pleas e help !!!

    Domestos Guest

  2. #2

    Default Re: Two MySQL database moddeling questions...


    Domestos wrote: 

    Based on your rules, and as long as there really aren't any many to
    many relationships, this looks like a viable structure...

    game(game_id*,map_id)
    map(map_id*,other map info)
    location(location_id*,location_type_id,map_id)
    location_type(location_type_id*,location_type)

    strawberry Guest

  3. #3

    Default Re: Two MySQL database moddeling questions...


    strawberry wrote: 
    >
    > Based on your rules, and as long as there really aren't any many to
    > many relationships, this looks like a viable structure...
    >
    > game(game_id*,map_id)
    > map(map_id*,other map info)
    > location(location_id*,location_type_id,map_id)
    > location_type(location_type_id*,location_type)[/ref]

    I hit send too quickly. I was just going to add that, in answer to your
    original query, yes - you can have a pk comprising the fks of two (or
    more) other tables. In fact, that's often how pivot tables are
    constructed. It's unnecessary in your schema but if you did have a
    many-to-many relationship, between maps and games, say - so that a game
    could comprise many maps and many games shared the same map/maps then
    you could record it as follows:

    maps_games(map_id*,game_id*)

    strawberry Guest

Similar Threads

  1. using mySQL database for my PC
    By Raous in forum MySQL
    Replies: 3
    Last Post: November 12th, 07:28 AM
  2. MySQL Update/ Join Questions
    By Cyber_will in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 3rd, 02:09 PM
  3. MySQL Database not retrieving the full database
    By geetha.veeraiah@gmail.com in forum MySQL
    Replies: 4
    Last Post: July 21st, 09:34 PM
  4. OT: where to get mySQL questions answered?
    By Zaphod in forum PHP Development
    Replies: 4
    Last Post: January 3rd, 02:35 AM
  5. Replies: 9
    Last Post: August 20th, 08:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139