|
|
Posted by Rik on 12/28/06 13:57
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_type_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
[Back to original message]
|