WEB Making a first website with a database, need some input

Discussion in 'OT Technology' started by jackpot, Apr 10, 2009.

  1. jackpot

    jackpot boo

    Joined:
    Dec 11, 2001
    Messages:
    32,855
    Likes Received:
    0
    Location:
    brooklyn, ny
    Basically I'm separating the data into two tables. Table one is general info and 2nd table is the lookup table for a more 'advanced' search. I figure if I put everything into one table, it'll be huge (I'm planning on having over 500 schools) and will take forever to lookup information and do searches.

    [​IMG]

    Thanks.
     
  2. biawokauns

    biawokauns New Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    Anywhere you have duplicate data, you should be using a foreign key. You can also break shit down, here are some examples.

    Code:
    Table 1:
    School name
    School loc
    
    Table 2:
    School ID
    Program
    
    OR
    Code:
    Table 1:
    School name
    City ID
    State ID
    
    Table 2:
    School ID
    Program
    
    CITY Table:
    City 
    
    STATE Table:
    State
    
    
    all kinds of different ways to model this, but it depends how you want it
     
  3. jackpot

    jackpot boo

    Joined:
    Dec 11, 2001
    Messages:
    32,855
    Likes Received:
    0
    Location:
    brooklyn, ny
    I know you can break it down, but I don't want a bunch of tables floating around. Your first example looks just like mine. My question was basically if the way I have it is efficient and good programming. Since this is my first project involving DB's, I want to have a good start.

    Thanks for the help.
     
  4. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
  5. jackpot

    jackpot boo

    Joined:
    Dec 11, 2001
    Messages:
    32,855
    Likes Received:
    0
    Location:
    brooklyn, ny
    You call that small? Assuming each school has 10 programs and I have 500 schools, that's 5000 rows. And don't forget, I'm only using 2 tables, not 6 like in the example you posted.
     
  6. biawokauns

    biawokauns New Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    you don't have to, but when you spec your tables initially you should noramlize em, then go back and see what you need and what is excessive
     
  7. biawokauns

    biawokauns New Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    your DB schema has duplicate data, mine doesnt
     
  8. biawokauns

    biawokauns New Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    5k rows is peanuts :o
     
  9. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC
    The biggest problem here imo is data consistency. Say you had 2 schools in NY city

    School ID | School Name | School Location
    ---------------------------------------------------------
    1 | Some school in NYC | New York, NY
    ---------------------------------------------------------
    2 |Another school in NYC | New York, New York

    Now you want to do a search of what schools are in New York city and do a query on location='New York, NY' ..... it's only going to return one school.

    I agree there should def be foreign keys for location info, but I usually just use a single "denormalized" table for locations. Heres a zipcode table you can use if you want.
    http://dev.andrewstallings.com/temp/zipcodes.sql

    It's not perfect, but should get the job done in this case. It's using the zipcode as the primary key, which really shouldn't be as there's often more than one city in a zip code.
     
  10. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC
    microscopic peanuts :eek3:
     
  11. intrktevo

    intrktevo New Member

    Joined:
    Oct 18, 2004
    Messages:
    5,781
    Likes Received:
    0
    Location:
    UCF
    Where did you hear that? It's not true at all. The following will return all records where the location is new york, ny

    SELECT * FROM Schools WHERE location='New York, NY'

    I would recommend splitting the location into, city and state columns
    Also, when you make your database, stay away from columns with spaces in the name
     
  12. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,651
    Likes Received:
    15
    Location:
    Atlanta
    Table 1:
    Table 2:
    Table3:
    Table4:
    Table5:
    table6:
    table7:
    Table8:
    Table9:
    technically this would be fully normalized (unless i missed something.) It'd be the right way to do it for a large DB, but for something with only 500 entries, your way wouldn't be bad.
     
  13. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,651
    Likes Received:
    15
    Location:
    Atlanta
    This
     
  14. DMClark

    DMClark Active Member

    Joined:
    Dec 22, 2001
    Messages:
    37,589
    Likes Received:
    0
    Lol this just reminds me of how shitty my first database design was. It is still in use today lol should really redo it.
     
  15. ge0

    ge0 New Member

    Joined:
    Oct 31, 2005
    Messages:
    8,398
    Likes Received:
    0
    Location:
    JERSEY
    linker tables are your friends
     
  16. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    Yes, I would consider that a small web application.

    Servers, mysql, etc, are extremely fast these days so unless you're mining out 50,000 rows of data in a query, the need for specific normalization in an application as small as yours isn't that necessary in my opinion.
     
  17. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC
    I think you misunderstood what I was trying to say.
    Say you have the following table "schools" with the following 4 records

    mysql> select * from schools;
    +----+-----------------------+--------------------+
    | id | school | location |
    +----+-----------------------+--------------------+
    | 1 | School in NY | New York, NY |
    | 2 | Some School | Chigago, IL |
    | 3 | Cool School | Atlanta, GA |
    | 4 | Another School in NYC | New York, New York|
    +----+-----------------------+--------------------+
    4 rows in set (0.00 sec)

    say you wanted to query all schools in New York City, school # 4 is in New York City, but it's not returned because there's nothing forcing the location field to be entered consistently.

    mysql> select * from schools where location='New York, NY';

    +----+--------------+--------------+
    | id | school | location |
    +----+--------------+--------------+
    | 1 | School in NY | New York, NY |
    +----+--------------+--------------+
    1 row in set (0.00 sec)



    If you were using the zip code table I posted and the zip as the primary key, it would solve problems like this

    mysql> select * from schools;
    +----+-----------------------+-------+
    | id | school | zip |
    +----+-----------------------+-------+
    | 1 | School in NY | 10270 |
    | 2 | Some School | 90210 |
    | 3 | Cool School | 28213 |
    | 4 | Another School in NYC | 10174 |
    +----+-----------------------+-------+
    4 rows in set (0.00 sec)


    mysql> select * from schools where zip in (select zipcode from zipcodes where city='NEW YORK' and state='NY');
    +----+-----------------------+-------+
    | id | school | zip |
    +----+-----------------------+-------+
    | 1 | School in NY | 10270 |
    | 4 | Another School in NYC | 10174 |
    +----+-----------------------+-------+
    2 rows in set (0.04 sec)


    That make better sense??
     
  18. intrktevo

    intrktevo New Member

    Joined:
    Oct 18, 2004
    Messages:
    5,781
    Likes Received:
    0
    Location:
    UCF
    I did yeah.

    Having city, state, zipcode lookup tables will probably be your best bet at this point in my opinion.
     

Share This Page