473,394 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Advice on Data

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
Dec 28 '06 #1
5 1204
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
Dec 28 '06 #2
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...

Dec 28 '06 #3
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
Dec 28 '06 #4
>>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
Dec 28 '06 #5
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
Dec 28 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: David Bear | last post by:
I would like some advice on what crypto lib to use. I am writing a cgi app that will store sensative data in a backend postgresql server. I have some simple numeric data I would like to make as...
2
by: Andrew | last post by:
I am starting my first C# project and have a design issue which I would appreciate some advice about. I am wondering whether to use dataset to pass information between components or if I should...
0
by: BCM | last post by:
I've been asked to display some meter data on the web and I need preliminary advice. We have several hundred meters on site, with a system that writes out xml files at 10 second intervals...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
4
by: Heath Kelly | last post by:
I need advice on correct usage of ADO.NET in an ASP.Net environment. I have an ASP.Net application that accesses data through a referenced class library. Things start to break down when multiple...
1
by: BCM | last post by:
I've been asked to display some meter data on the web and I need preliminary advice. We have several hundred meters on site, with a system that writes out xml files at 10 second intervals...
2
by: Manny | last post by:
Hello I'm fairly new to ASP.NET and need your advice, I need to display monthly/weekly table information on the same page. The user should be able to select first from a month (from a month link...
3
by: Sigmathaar | last post by:
Hi, I'm need some advice about lists and vectors. I'm doing a program who needs to have sequential access of a non ordered unit of objects whose size decreases almost each time the sequence is...
13
by: Alan Silver | last post by:
Hello, MSDN (amongst other places) is full of helpful advice on ways to do data access, but they all seem geared to wards enterprise applications. Maybe I'm in a minority, but I don't have those...
23
by: JohnH | last post by:
I'm just recently come to work for an auto brokerage firm. My position involves performing mysterious rites, rituals and magick in order to get information out of their access database. This is...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.