Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 8 of 8
  1. #1
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    Ok, I'm stuck on how I can proceed with my project. On my website, each player can create a fake company and run it, like a business simulation. Each company will need to buy land in cities. Now I need a way of storing which company owns how much land and in what cities. I could do it by having a new table with the names of each city as columns and the name of the company as a column, i.e:

    Company | Atlanta | New york | Houston | Los Angeles
    -----------------------------------------------------
    Comp. #1| 10 | 5 | 20 | 7
    Comp. #2| 5 | 10 | 5 | 15

    The problem with that, is that I have a LOT of cities, and I am constantly adding new cities. It would be very time consuming to keep updating that table. I could create a script I suppose, but I know there has to be a better way to do it.

    What I was thinking of doing was adding one or two columns in the cities table I already have, that lists what companies own how much of that city. But I'm not sure how I would select or store a list, mabye seperated by commas, in a single field. Here's my current setup for my cities table:

    CREATE TABLE `city_list` (
    `city_id` smallint(8) unsigned NOT NULL auto_increment,
    `city_name` varchar(20) character set utf8 collate utf8_bin NOT NULL default '',
    `population` varchar(20) character set utf8 collate utf8_bin NOT NULL default '',
    `image` varchar(20) character set utf8 collate utf8_bin NOT NULL default '',
    `acres` varchar(20) character set utf8 collate utf8_bin NOT NULL default '',
    `sph` varchar(10) character set utf8 collate utf8_bin default NULL,
    `acre_price` varchar(5) character set utf8 collate utf8_bin default '800',
    `move_price` varchar(7) character set utf8 collate utf8_bin default '1000',
    `pic` varchar(20) character set utf8 collate utf8_bin default NULL,
    `total_acres` decimal(40,2) default NULL,
    `continent` varchar(30) default NULL,
    `country` varchar(35) default NULL,
    `lon` varchar(30) default NULL,
    `lat` varchar(30) default NULL,
    PRIMARY KEY (`city_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=102 DEFAULT CHARSET=latin1

    Does anyone have any ideas? Or mabye a completely new approach I haven't thought of?

    Unk

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Proper 3rd normal form allows you to have multiple land units per company per city, with specific info about each.

    player
    player_id | name | other personal info

    company
    company_id | player_id | company_name | other company info

    city
    city_id | name | other city info

    land_type
    land_type_id | name

    company_land
    land_id | company_id | city_id | land_type_id | land_name | land_size | address?

  4. #3
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    In your above example, are Player, Company, City, Land_Type and Company_Land different tables? I'm not 100% sure about what exactly 3rd normal form is.

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Yes, in order to maintain proper data integrity (no duplicable / contradicting values), you should maintain different tables for each. This is known as 3rd normal form. The added benefit is that every time you add a city to your city table, you can immediately add company_land to that city.

  6. #5
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    Ok I think I get it. I already have all those tables set up, it's just a matter of re-routing them to work like that.

  7. #6
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    In your example, when say Company A buys 10 acres in, say, Atlanta, an entry would be added to the company_land table correct? What if Company A then bought another 10 Atlanta later? The table would just need to be updated, or would I have to make a whole new entry?

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    That depends on how you want to structure your data.

    I would personally create each purchase as a separate entry, so that you have individual land plots each owned by one company.

    You could then use COUNT() to determine how many plots they have in one city, and SUM() to determine how much acreage they have.

    You could also have each company sell individual land plots to other companies, which would add interest and depth to the game.

  9. #8
    Member Unknown98's Avatar
    Join Date
    May 2008
    Location
    Houston
    Posts
    46
    Member #
    16866
    Ok, that makes sense. Thanks!


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:09 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com