Re: Database Design - Request for Comment

On 11 Jul 2001 14:54:12 -0700, matthew.mark_at_edwards.af.mil (Matthew Mark) wrote:

>abracad_at_hotmail.com (abracad) wrote in message news:<3b478afe.3519603_at_news.freeserve.net>...
>> I am building a searchable online photographic library and have
>> produced an initial design for the database structure.
>>
>>
>> PHOTO ------ PHKW ------- KW
>> |
>> |
>> LOCATION-----LOCPLACE-----PLACES
>>
>> PHOTO: ID, description, locID
>> PHKW: phID, locID
>> KW: keyword, ID
>> LOCATION: fullLocation, ID
>> LOCPLACE: locID, plID
>> PLACES: placeName, ID
>>
>> fullLocation consists of something like: Shinjuku, Tokyo, Japan
>> placeName consists of indvidual place names eg Shinjuku; Tokyo; Japan;
>> Europe
>>
>> The database is to be constructed with Microsoft Access. Additional
>> fields e.g. author and date may be added later.
>>
>> I would be most grateful for any comment / suggestions for
>> improvement.
>>
>> abracad_at_hotmail.com
>
>It's hard to comment since the structure is very important based to
>how you intend to search for data. What is the relevance or each of
>the tables. What's the difference between Location, Locplace and
>places? Besides the relationships, it's very important to have field
>on which you intend to search, indexed. Also, use picklists or lookup
>values whenever possible. This way your data will be much more
>friendly, sortable and searchable.

At present is intended that the data will be searched by subject keyword, and/or location. Location holds the full string that would be displayed with a picture e.g. "Brighton, Sussex, England, Britain, UK". It in intended that it will be output to the user.

Places contains just a single placename, eg "Brighton" would be one record, "Britain" another.

Locplace holds the key values that model the many to many relationship.

It is intended that places will be searched which will point to n locations. The photographs holding the corresponding IDs will be retrieved.

When completed the number of place names may run into thousands therefore a picklist won't be practical. Will it? Received on Thu Jul 12 2001 - 01:17:41 CEST