Re: Novice / Intermediate user seeks help in structuring data / tables
Jay,
First off I want to extend PROFUSE THANKS for your THOUGHTFUL, GENEROUS, and KIND HELP!!
Your reply, and the work you put into it (albeit for you, it was merely "whipped up"... nonetheless...) is so very nice of you!
okay, i haven't touched your SQL yet so i'm probably speaking too soon, but i wanted to let you know that i do understand a bit about normalization, so i know where you're comin' from there-- at least i hope so. ;)
also, i've also done projects using relational database (using up to 6 tables)... not that it's alot or anything...hehe, but i'm just trying to give you a better idea of where i am in terms of experience-- i kinda wish i would have said all that before, and perhaps you would have given me equal effort w/out having to spend the time on the normalization / relational db / key values bit. ;)
(i guess i was calling "Key Values" as "Unique_Identifier"... same thing... right? knowing that there can be more INDEX's than just the auto-increment, primary key in any given table of course, as long as they are so named)
i guess any experience that i have is not all that obvious from the looks of things here, though, huh? haha. well, see-- this data came to me in the form of an Excel Spreadsheet-- the timesTable ONLY, not the StopsTable-- that one is mine! hehe. the table w/ all of the keys as field-names is the "migrated" data from Excel-- (migrated via "Intelligent Convertors Excel to MySQL", which only did the first 5 rows cause i was demo-ing it!) I kept the existing Excel layout because i thought it could be useful in the sense that the data w/in those fields is correct, and the data which populates each of those columns is also correct respectively-- so, i think what i really need is just more tables.
(note: to maintain sanity while reading... remember-- there are two tables "timesTable" and "stopsTable"-- although i may spell or type them differently herein)
Before we begin here, please take one more look at my existing tables and take note of these 3 key qualities of these tables i've shown you:
a). "en_route" of the stopsTable is an arbitrary, PRIMARY_KEY, as is also the "time_id" an arbitrary auto_incrementing PRIMARY_KEY. (en_route will never increment as there will be no stops added to the routes, although it is set to auto_increment to allow for such modifications)
b). "stops_code" of stopsTable mirrors the fieldNames of timesTable, so "stops_code" is also a Unique_Index based on the static data of the bus-stops, each of which has their respective bus-arrival-time represented in the rows of the timesTable
c). you see 2 tables which represent ONE BUS ROUTE. i am currently working with SEVERAL BUS ROUTES, each of which should ultimately have identical table structures. currently, i have 4 tables here for 2 separate routes-- starting small to keep sane.
the ROWS in the timesTable ARE POPULATED in the timesTable W/ TIME DATA-- eg. stop_1a values( 7:00, 7:45, 8:15, etc) -- and stop_1b values(7:05, 7:50, 8:20 etc)-- (note: 5 mins apart between busses for sake of illustration) so i can tell you (at this point, only manually can i do this, not via MySQL) at which geographical location "bus A" is relative to "bus B", based on an existing auto_increment field name "time_id", which is the "PRIMARY_key_value" of their respective timetables, each of which look basically identical in structure to what you've seen, except the field names represent different geographical locations (in other words, "bus A" has a time table w/ fields named col1=stop_abc, col2=stop_cde, while "bus B" has a time table w/ fields named col1=stop_wxy, col2=stop_xyz, and so on)-- so, although it appears as though my key_value names don't belong as the field names in that particular table (the timeTable)-- in actuality, at least from my experience-level perspective, i think that this table is an essential piece of the whole relationship which will make up the core of the app.
the reason i came here is that i guess what i'm trying to do-- is take those field name values and stick 'em in a query against the stopsTable-- so that i CAN do w/ mySQL what i explained above about the geographical locations. the way i see it, i NEED this timeTable's fields to have the times identified by those Key_Values-- the same values which represent each busStop, but since it is not currently MySQL Query friendly in terms of the relational database structure / app i'm trying to build, hence my dilemma. what is it that i need in this 3rd table? i need a table that will let me in some way query those fields-- but of course those fields must be in a column themselves somewhere. WHY is this so DIFFICULT for me?!? am I nuts? am i just not listening to what you're telling me here, or what? i think i need to make that third table-- but how? i'm sure i'll need more tables that three in the end, but for now-- i'm stuck. hmph!
in the meantime, i'm going to take a better look at your suggested tables.
OH! and the other thing that i noticed, which was misunderstood-- the "Fare-Free"-- it IS an ENUM. it means "can you ride the bus for free here or not". certain portions of the route, patrons may ride for free-- hence "NO.." or "YES, this is a fare-free stop".
Jay, again-- thank you sooo much for your help. i hope that you get the chance to read this, and maybe you (or anyone else out there of course) will take another shot at giving me a hand now that i've given more info. thanks!
-- -- -- --
Use CSS [ content:" ?? " ] for Unicode + Control Chars (e.g. newline, etc)... Gain understanding of Codepage, Charset, Encoding, Keymapping... Generate CSS and JavaScript on-the-fly with PHP... Tales from the Communications Dept @ NoviceNotes.Net
Edited 11 time(s). Last edit at 12/08/2005 12:21AM by Jeff Sabarese.