Advice on Data

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi all,

A question on Data Modelling...

Not strictly a php question [but linked to it with MySQL] If there is a  
MySQL NG out there with frequent users please point it out to me and I will  
re-post there... otherwise if you can help I would appreciate it greatly...


Basically I am trying to design a simple database model and getting into all  
kinds of bother. Please whack me with a noob stick when you feel fit :)

I have several entity's(tables) called...

Map (*map_id*, map_name)
Location (*location_id*, &map_id&, location_name)
Location_Type (*location_type_id*, &location_id&, &map_id&,  
Coast_Type (*coast_type_id*, &location_id&, &map_id&, coast_type_name)

[* surrounded attibutes are the entity primary key]
[& surrounded attributes are a foreign primary key]

Obviously one location relates to only one map

But the Location_Type and Coast_Type can relate to many locations on many  
map. When I try to do this it causes all sorts of bother in the Location  
entity. I.e. several foreign key 'map_id' attributes are created in the  
Location entity.

Am I going about if the correct way?



Re: Advice on Data

BigBadDom wrote:
Quoted text here. Click to load it


I'm not sure what the relationship of your data is, probably has something
to do with me not being a native speaker, but as far as I can tell:

Map (*map_id*, map_name)
Location (*location_id*, &map_id&, location_name)
Location_Type (*location_type_id*, location_type_name)
Coast_Type (*coast_type_id*, coast_type_name)

More tables, but easier relating, and less repetitive. At least, if my
assumption that a Location can have several Location_Types is correct. If a
Location can have only one Location_Type (and/or only one Coast_Type), it
should be:

Map (*map_id*, map_name)
Location (*location_id*, &map_id&,
Location_Type (*location_type_id*, location_type_name)
Coast_Type (*coast_type_id*, coast_type_name)

Rik Wasmus

Re: Advice on Data

Quoted text here. Click to load it

Thx Grtz,

Each Location can only have one of many 'Location_type_id'

However there is a further constraint where I would like the 'location_type'  
and 'coast_type' to be dependant on the 'map_id' and 'location'.


'Location' entity data example...
location_id            map_id            location_type_id
1                            1                    1
2                            1                    1
3                            1                    2
4                            1                    2
1                            2                    1
2                            2                    2
3                            2                    3

'Location_type' entity data example...

location_type_id        map_id        location_type_desc
1                                1                Water
2                                1                Grass
3                                1                Forest
1                                2                Water
2                                2                Wetland
3                                2                Grass
4                                2                Rock

'location_type_id' and 'map_id' would be the joint primary key for the  
'Location_type' entity. And using the 'location_type_id' and 'map_id' from  
the 'Location' entity I would be able to find the 'location_type_desc'


Location Type for 'location 2' on 'map 1' = Water
Location Type for 'location 2' on 'map 2' = Wetland
Location Type for 'location 3' on 'map 2' = Grass

Re: Advice on Data

BigBadDom wrote:
Quoted text here. Click to load it

Huh? Who?

Quoted text here. Click to load it

Euhm, you're losing me here...
Perhaps a less abstract description of what you're trying to accomplish is
in order. I cannot for the life of me think how a location_type could be
map dependant. Sand is sand, water is water, wether it's on map A or map
Z.... A location can have a type, and a location can be on a map. Which map
should not be relevant to the type.

Quoted text here. Click to load it

Hardly workable, or are you trying to double data? You've got a list of
types (Water,Grass,Forest,Wetland, etc.). This is data you don't wish to
repeat, but give an ID, so you can reference this elsewhere.

So you'd have a table 'location_types:
1 Water
2 Grass
3 Forest
4 Wetland
5 Rock

If these ID's are now dependant on the specific map (so, on map A 3 is
forest, but on map B 3 is Grass, I seriously urge you to reconsider this,
and make types independant of maps. A simple transition now will save you a
lot of headache in the future.

Quoted text here. Click to load it

I would never, ever, give locations the same id, and let them depend on a
different 'map_id'. In would number all the locations continuously, and
also give them a map id. The point is id's are meant to be unique, at least
for the data they represent. So, it would be no problem to have a location
#23 and a location_type #23, but you're making it very difficult for
yourself when you have a different location #23 on map A, map B, etc..,
similar for having different location_type_id's for different maps.
Rik Wasmus

Re: Advice on Data

Quoted text here. Click to load it

Sorry thought you signed off as Grtz - it should be Thx Rik :)

Quoted text here. Click to load it

Thats what I am tryiong to confirm - whether my design is good or bad....

Okay a better - more abstract - way to describe what I am trying to acheive  

I have a number of games, each game has a pre-defined associated map.
Each map is made up of locations
The locations will have a type.

Maps can be anything from actual world maps to space-ship layouts to random  
dots on a page.
Each location-type defines these map locations in a certain way.

i.e. on one map the map location types may be Grass, Water and Rock but on  
another map they may be described as Bridge, Path and Building and on  
another they could be Left Corner, Right Corner and Center etc..

Maybe that is my design fault. Maybe I should just create a long list of  
location-types that can be used with any map.



Re: Advice on Data

BigBadDom wrote:
Quoted text here. Click to load it

Hehe, you're welcome.

Quoted text here. Click to load it

Well, less abstract... Unless I'm hugely mistaken about a curious
difference in meaning in English & Dutch if the word 'abstract' :-)

Quoted text here. Click to load it

Well, when it's about games the starting point of map-specific location
types make more sence. I'd still opt for the totally seperated
location-types though. Even game designers manage to think up 200 or more
types it will still be more effective. Depending on what you want to do
with the data there is the possibility you want to know which types are
relevent to a map, but then I'd just use a relational table of maps &

You might however, as 'locations' in games have very different aspects all
together, 'split' those aspects. Different tables for
represantation/patterns (grass, water, dungeon), boundaries (left,
top,right,bottom, or a combination of those), max amount of players,
etc,etc (as you already seem to have done with the 'coast-type' split).
That way, a lot of repetition can be avoided, and still you'll have a very
flexible system.

It will depend on how many games you want to enter and how much they
differ. For starters a single location-type will do.
Rik Wasmus

Site Timeline