SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict whofarted's Avatar
    Join Date
    Aug 2001
    Location
    lost, If you find me please return me to St.Louis
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    setting up a db...need ideas for layout ;)

    I'm getting ready to add, to a phpnuked site, a section to track certain things for our members.

    We're going to do some... I guess stat tracking for a flight sim.

    I'm using phpnuke for the site & using the normal signup for their personal info like their name, location, ICQ#, AIM, etc. But i'm going to use a new table(s) for other things.

    Here's what i'm looking to do:

    I want to track these totals:
    Hrs. flown, Hrs. day, Hrs. night, & maybe hrs per plane day/night.

    Also individual flights:
    Hrs flight took, Day/night, Type of plane, number of passengers, pounds of cargo, etc.

    I pretty much suck at designing a db soooo...
    Here's the question:
    When doing this how should I setup the db?

    1.) One table for all things
    2.) one table for plane types & one table for the rest of the stats
    3.) Your suggestion

    Any help would be appriciated. TIA
    You smell something?

  2. #2
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I do have a couple of suggestions. I have three tables within the database.

    planes
    pid pk
    type
    maxcargo
    maxpassengers

    flight
    flightid pk
    flightnum
    pid fk
    numpassengers
    pndscargo
    start
    finish

    Total time can be found out by working out the difference between the timestamps used in start and finish.

    These stats can be contained within a seperate table or calculated with individual flights. For speed and effeiciency these values will be better contained within a seperate table

    totals
    tid pk
    pid fk
    hrsflown
    hrsday
    hrsnight
    totalcargo
    totalpassengers

    You will see I did adlib a little bit. To make this as extensible as possible yo want to guess what your clients may need before they ask it. Hopefully this gives you some ideas.
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  3. #3
    SitePoint Addict whofarted's Avatar
    Join Date
    Aug 2001
    Location
    lost, If you find me please return me to St.Louis
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK cool, i'm getting the idea.

    Whats the pk & fk about though?
    flight
    flightid pk
    flightnum
    pid fk
    is that "f"light key & "p"ilot key?

    i'm just wondering if it's important like how to associate the tables with eachother or whatever.

    P.S. as far as flight time it tells them at the end of the flight something like 2.3 hrs. But yeah i get the point.
    Last edited by whofarted; Apr 21, 2002 at 23:15.
    You smell something?

  4. #4
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by whofarted
    OK cool, i'm getting the idea.

    Whats the pk & fk about though?

    Primary key and foreign key. It is how the tables relate to each other. The primary key has to be unique in every way (numerical normally). The way other tables 'relate' to it is via the foreign key. For example.

    planes
    pid - 1199
    type - 747
    maxcargo - 1 tonne (whatever)
    maxpassengers - 245

    flight
    flightid pk - 1
    flightnum - 5464
    pid fk - 1199
    numpassengers - 34
    pndscargo - 300 pnds
    start - 20020323123200
    finish - 20020324123200

    Can you see how it relates. When you do a query you can 'connect' the databases via the pid column.


    You could take this even further. Each flight number only applys to one start/destination combination if I am not mistaken. You could create another table with the pk being flightnum. This would (when queried correctly) connect that table to the main table. You would have a constant lookup for where when why and how much that place could and did carry.

    Get the idea

    Try these article. You really should get a better understanding of database normalization. It is a powerful and useful tool when programming web apps.

    I will assume by the information given you are using mysql.

    http://www.devshed.com/Server_Side/MySQL/Normal
    http://www.phpbuilder.com/columns/barry20000731.php3
    http://www.phpbuilder.com/columns/joe20010104.php3
    http://www.phpbuilder.com/columns/tim20010110.php3
    http://www.phpbuilder.com/columns/tim20010117.php3
    http://www.phpbuilder.com/columns/allan20010115.php3

    This next site is running toooo slow for me to bother. Look for articles called 'mysql wizardry' and 'mysql table joins'.
    http://www.devshed.com/Server_Side/MySQL

    Best of luck. It seems like and interesting project you are working on
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  5. #5
    SitePoint Addict whofarted's Avatar
    Join Date
    Aug 2001
    Location
    lost, If you find me please return me to St.Louis
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok I'm getting it now & i'll look at all those links you gave

    Like I said I'm not really good with dbs. I've been putting most of my effort towards learning php, now it time to get better aquanted with mysql though i guess

    Thanks for the help! I'm off to start reading
    You smell something?

  6. #6
    SitePoint Addict whofarted's Avatar
    Join Date
    Aug 2001
    Location
    lost, If you find me please return me to St.Louis
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I seemed to be following it while reading the articles but i'm totally messing it up when trying to do it for myself.

    Here's what i've got right now but as I was typing it up I was changing things around again & again.

    Can you try to see what i'm doing and untie my brain knot?

    here's my db stuff now. (don't laugh please)

    pid in fsim_pilots would come from nuke_users & use their user id from the website BTW.

    #
    # Table structure for table `fsim_pilots`
    #

    CREATE TABLE fsim_pilots (
    pid tinyint(4) NOT NULL default '0',
    flightid tinyint(4) NOT NULL default '0',
    rank varchar(20) NOT NULL default '',
    PRIMARY KEY (pid)
    ) TYPE=MyISAM;


    #
    # Table structure for table `fsim_flight`
    #

    CREATE TABLE fsim_flight (
    flightid tinyint(4) NOT NULL default '0',
    fltnum tinyint(4) NOT NULL default '0',
    planeid tinyint(4) NOT NULL default '0',
    hrsday tinyint(4) NOT NULL default '0',
    hrsnight tinyint(4) NOT NULL default '0'
    ) TYPE=MyISAM;


    #
    # Table structure for table `fsim_planes`
    #

    CREATE TABLE fsim_planes (
    plane_id tinyint(4) NOT NULL auto_increment,
    pid tinyint(4) NOT NULL default '0',
    ptype varchar(25) NOT NULL default '',
    maxcargo tinyint(4) NOT NULL default '0',
    maxpass tinyint(4) NOT NULL default '0',
    PRIMARY KEY (plane_id)
    ) TYPE=MyISAM;


    I'm figuring to get total I could just select all flights by pilot id & get the totals. Then for overall in the corp. I could select all flights & get total day/night then day+night = total overall. Same with passengers & cargo.

    I keep setting it up, then changing it over & over & getting lost
    You smell something?

  7. #7
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats a little different than the one I layed out...and it isn't bad...actually you learn quickly...

    The way I had it setup was a plane table. This contains the information about the plane such as capacitiy etc.

    I then had one for flights which contained the information on the flights themselves and their individual carries etc..

    Then I had one for totals. The reason I did it this way is for speed. It is easier to keep track of totals in its own table. You can do this because it will be easiesr later to manipulate those totals to calculate statistics. Some people prefer to use mysql calculations to do this and it is possible but I prefer to keep a seperate table for easy access at all time. The information will be kep updated by the server sided language you use everytime some information is inputted. Now to your problem.

    Right now you seem to be going in a circle. It took me a bit to figure out. Maybe I am too tired. This is what you are doing. I am going to try to explain this I hope I make sense

    In the pilots table you are have

    pilots
    pid pk
    flightid fk
    rank

    Then in planes you have
    planes
    planeid pk (you forgot to make this a primary key in your table though)
    pid fk
    ptype
    maxcargo
    maxpass

    So far so good. This work well. These can join and you can find out many pieces of information such as which pilot flys which plane. But nothin about the flights. Here where you have created a circle. You have flightid in pilots referencing flights and planeid in planes referencing flights. I imagine this can be done but there are better ways. You can have one pilot per plane which would then reference the flights table like so

    pilots planes flights
    pid --------------------------> pid
    planeid----------------->planeid
    [note] THIS DIDN'T TURN OUT RIGHT. If you don't understand this part I will throw a pic together in photoshop so you can visualize it
    [/note]

    Hopefully that makes sense. The reason why this doesn't make logical sense is because you can have multiple pilots of one plane over many flights, correct? So you have a couple of options. The first that came to me was a lookup table. It works like this

    pilots
    pid pk
    rank

    lookup
    pid fk
    lid pk

    planes
    planeid pk
    lid fk
    ptype
    maxcargo
    maxpass

    flights
    flightid pk
    fltnum
    planeid fk
    hrsday
    hrsnight

    You don't need to have flight table referencing or joining with the pilots table because you can fetch all of the information when you join.

    Like I said there are a few ways to do this. This is rather sequential and I am sure if I sat down a little longer I could work out another way to create this. ha. I just did.

    You could also use the pilot as the primary table (this way would cut down on tables too). This is also similar to your method but removing the looping reference. Dang this is gonna be a long post.

    pilots
    pid pk
    rank

    planes
    planeid pk
    pid fk
    ptype
    maxcargo
    maxpass

    flights
    flightid pk
    fltnum
    pid fk
    hrsday
    hrsnight

    That seems better. Now the other two tables don't relate to each other except through a pilot. Now you should be able to create a query to select your totals based on any information you want.

    Well I am gonna leave the rest till tommorow. Those are two seperate ways to acheive the same results. Number 2 is more efficient due to its size (it will be smaller).
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  8. #8
    SitePoint Addict whofarted's Avatar
    Join Date
    Aug 2001
    Location
    lost, If you find me please return me to St.Louis
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of I'd like to thank you for your time. That was quite a big post

    After a while I walked away & watched some TV, then came back after my brain slowed down a little.

    Now I've setup the db a little different & put some dummy data in it to play with it.

    I did setup planeid in planes as the primary key (appearently after I posted )

    As far as this goes I'll tell you (now that I have a better feel for it) how this is working.

    Basically I need all the info to be tracable to each pilot. Any pilot can fly any plane, So I figured I should make 1 table fsim_pilot that hold all their info like rank, their id number, & total amount of money made. (I know this is a new twist I forgot to mention ) I'll get to that in a bit.

    Another thing I wanted to mention was all flights will be handled as full loads. IE: if a plane can carry 150 passengers than each trip with that plane will contain 150 passengers.

    so here's what my db look like now:

    TABLE fsim_flights

    pid <= pilots id
    plane_id <= planes id
    fltnum <= flight number (this is whatever they want to use, it can be any number/char ie: 8043J)
    hrsday <= hrs for day flight
    hrsnight <= hrs for night flight
    Note: pid here is going to be from nuke_users, therefore it's not auto incremented but probably should be unique eh?


    TABLE fsim_pilots

    pid <= pilots id
    prank <= rank (like co-pilot, captain, etc.)
    pmoney <= How much money they've made total
    PRIMARY KEY (pid),
    UNIQUE KEY pid (pid)


    TABLE fsim_planes

    planeid <= planes id number
    name <= Name of plane like Lear 45
    passload <= Number of passengers
    cargload <= pounds of cargo
    PRIMARY KEY (planeid)

    Flights are associated to pilots via "pid" in both flights & pilots, & planes are associated to flights via fsim_flights.plane_id = fsim_planes.planeid.

    I noticed you had a field "pid" in planes, Why? Aren't the planes only associated to the flights?

    To get the info is pretty easy so far. And i'm not having any problems so I "think" this is ok. Unless you can see a problem I'm not forseeing.

    I figure i'll do as you said and create a new table for totals over all for the airline, plus i'll make another table for the payscale.

    Here's a snapshot to show you a sample of data being retrieved with some dummy info. This associated all flight with my fsim_pilots.pid.



    So far everything is going great, it's been a good learning experience, I learned how to do my first table join using LEFT JOIN & I seem to be getting the data out correct, my first aliasing (select something as this), and used sum() for the first time in a MySQL query.

    Thanks again for all your help
    Last edited by whofarted; Apr 23, 2002 at 23:03.
    You smell something?

  9. #9
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Congratulations. You seem to have the hang of it. Like I said there is many ways to acheive the same thing. I undertood regular joins instantly and it took me 3 months to find a use for LEFT JOIN. One day (with a really large query) it clicked and opened up many more doors.

    The only other thing I can tell you is learn how to design a flow chart. It makes understanding the database much easier to follow. You will also be able to see weakness's before they happen ...

    You asked this question.
    I noticed you had a field "pid" in planes, Why? Aren't the planes only associated to the flights?
    I don't know are they? Sorry this is your question to answer not mine.

    Once again congratulations. That is exactly the reason why I try not to give answers but methods. Now you understand sql theory.
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  10. #10
    SitePoint Addict whofarted's Avatar
    Join Date
    Aug 2001
    Location
    lost, If you find me please return me to St.Louis
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll get help doing flow charts from my bro-in-law, He's a programmer & i've heard him talk about those before. I guess i'll ask him about them, only this time I'll be more interested.

    Thanks again for the help
    You smell something?


Bookmarks

Posting Permissions

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