Advice on Data

[BigBadDom]

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&,
location_type_name)
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?

--

Thanks,
BigBadDom

[Rik]

BigBadDom wrote:

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...

comp.databases.mysql

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&,
location_type_name)
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.

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)
Location_Location_Types(&location_id&,&location_ty pe_id&)
Location_Coast_Types(&location_id&,&coast_type_id& )

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_name,&location_type_id&,&coast_type_id&)
Location_Type (*location_type_id*, location_type_name)
Coast_Type (*coast_type_id*, coast_type_name)

Grtz,
--
Rik Wasmus

[BigBadDom]

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_name,&location_type_id&,&coast_type_id&)
Location_Type (*location_type_id*, location_type_name)
Coast_Type (*coast_type_id*, coast_type_name)

Grtz,

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'.

thus....

'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
etc...

[Rik]

BigBadDom wrote:

>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_name,&location_type_id&,&coast_type_id &)
Location_Type (*location_type_id*, location_type_name)
Coast_Type (*coast_type_id*, coast_type_name)

Grtz,

Thx Grtz,

Huh? Who?

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'.

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.

thus....

'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.

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.

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
etc...

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

[BigBadDom]

>>Grtz,

Thx Grtz,

Huh? Who?

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

>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'.

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.

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
is...

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.

Thoughts?

Thks
BigBadDom

[Rik]

BigBadDom wrote:

>>>Grtz,

Thx Grtz,

Huh? Who?

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

Hehe, you're welcome.

>>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'.

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.

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 is...

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

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.

Thoughts?

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 &
location-types.

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