Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    1

    Help in DB/table layout

    I need to setup a web database application. The application process I think I can handle. The database/table layout is a different thing. I've never used one before. Hopefully someone here can help.

    What I am trying to do is makeup a database of fishing resorts around the country. I have information on over 4,000 of them. I need to be able to give visitors the ability to search on name, location and all of the usual things. I also want to include places to eat nearby with the type of food such as french, italian etc. Needless to say it would need to have the ammenities for the resort, type of fishing available and species of fish caught there. Would also like to have the ability to search for other resorts in the same vacinity.

    I have the headings for this things I want in the db but do not know how to go about putting the right information in the correct tables. Also how to connect them together so people can search on whatever item interests them.

    Sounds like a lot to ask but I am asking. Maybe someone else has done something like this.

    Thanks so much in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    The rule of thumb is:
    The more normalised your database is the easier searching becomes.
    Have a few reads of this article and see how you get on. Post all your questions back here
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'll start you off with the following - I haven't checked it fully (or even partially) - but you'll need to decide the types for each field (id's are integers and names are varchar(40) ). See if your data fits into this layout first.

    If it does then we can pick the primary keys etc.

    Then we can do the search stuff so it covers everything.
    Code:
    -- lookup tables
    -- this will be populated once by hand and doesn't need an id field
    create table Country(
       name              varchar(40),
       primary key ( name )
    )
    
    -- this could be populated by hand or an admin screen
    -- Dorset in the UK
    create table Location(
       id                 int,
       name               varchar(40),
       country            varchar(40),
       primary key ( id )
    )
    
    -- this will be populated once by hand
    -- ie Deep Sea, River etc
    create table FishingType(
        fishingType         varchar(20),
       primary key ( fishingType )
    )
    
    -- this could be populated by hand or an admin screen
    -- ie Trout, Salmon etc
    create table Fish(
        name            varchar(40),
        extraInfo       varchar(255),
        -- season field ?
       primary key ( name )
    )
    
    -- this could be populated by hand or an admin screen
    -- ie Resteraunt, French Resteraunt, B&B, Tackle shop, Garage
    create table AmenityType(
        amenityType             varchar(40),
        amenityParentType        varchar(40),
       primary key ( amenityType )
    )
    
    -- this will require a screen for users to add new amenities
    -- you may want an approval field for you to allow the entry to be seen by the public
    create table Amenity(
        id                int,
        name              varchar(100),
        amenityType       varchar(40),
        tel               varchar(20),
        address            varchar(100),
        zip               varchar(12),
       primary key ( id )
    )
    
    
    -- this will require a screen for users to add new amenities
    -- you may want an approval field for you to allow the entry
    create table Resort(
       id             int,
       name           varchar(40)
       locId          int,
       primary key ( id )
    )
    
    
    create table ResortFishingType(
        resortId             int,
        fishingType          varchar(20),
       primary key ( resortId, fishingType )
    )
    
    create table ResortFish(
        resortId             int,
        fishName             varchar(40),
       primary key ( resortId, fishName )
    )
    
    create table ResortAmenity(
        resortId             int,
        amenityId            int,
       primary key ( resortId, amenityId )
    )
    
    
    -- any user can supply feedback once on a resort
    -- this is open to abuse though
    create table ResortFeedback(
       ip                  varchar(20),
        resortId           int,
       feedbackDate       datetime,
       feedbackTxt         varchar(255),
       score                int,
       primary key ( ip, resortId )
    )
    
    -- any user can supply feedback once on an amenity
    -- this is open to abuse though
    create table AmenityFeedback(
       ip                      varchar(20),
        amenityId              int,
       feedbackDate            datetime,
       feedbackTxt             varchar(255),
       score                   int,
       primary key ( ip, amenityId )
    )
    EDIT: cleaned up a little bit but not ran through MySQL
    Mike
    Last edited by mike_bike_kite; 09-05-07 at 13:45.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    This shockwave training exercise on referential integrity may help, also.

    Although it was written for PostgreSQL, it is VERY appropriate for many (if not most) databases.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It's odd that people will ask a question, let others respond and then not even give a thankyou / acknowledgement.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •