MySQL Forums
Forum List  »  MyISAM

Re: Novice / Intermediate user seeks help in structuring data / tables
Posted by: Jay Pipes
Date: December 14, 2005 11:33PM

Jeff Sabarese wrote:
> 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.

I still believe that the tables I laid out in my earlier post are the way to go here. The reason is that when the *name* of the field actually is a part of the data, then the schema will not be easy to work with and violates normalization laws (laws which serve to make your life easier... ;) )

I understand that you want to keep the Excel spreadsheet layout, however, remember that Excel <> RDBMS, and attempting to model your schema after a spreadsheet is *not* a good idea. What ends up happening is that you view the tables and rows in terms of the spreadsheet's cells and columns, and in fact they are not the same concept. Relationships between records in separate tables are the key to efficient schema and SQL code. There is no real concept of relationships in a spreadsheet, at least none that serves you any purpose in the database world. Stick to relationships, and viewing the data in terms of entities and relationships, and you'll be fine.

What I recommend here is to have a table timeTable in yoru database that serves *only* to be the receiver of the Excel to MySQL convertor. Once the data is in the MySQL table, use scripts of SQL code to convert from this spreadsheet format into the normalized format I describe above.

> 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!

It's only as difficult as you make it for yourself! ;) Give my schema a chance first!

> 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".

OK, then in this case, I believe it would be beneficial to make this small change to the RouteStops table I describe above:

ALTER TABLE RouteStops
ADD COLUMN fare_amt DECIMAL (6,2) UNSIGNED NOT NULL DEFAULT 0.00;

ALTER TABLE Routes DROP COLUMN fare_free;

Then, you want to put in the fare amount for this stop *to* the next stop, and leave the fare_amt value 0.00 where the ride is free to the next stop.

With the fare information stored this way, it's then possible to figure out what the fare would be at any given part of any given route. For instance, let's say we wanted to know the whole fare for the Route ID #3:

SELECT SUM(fare_amt) as fare_amt
FROM RouteStops
WHERE route_id = 3;

or, we could find the stops where the rider can get to the next stop for free:

SELECT s.name
FROM RouteStops rs
INNER JOIN Stops s
ON rs.stop_id = s.stop_id
WHERE route_id = 3
AND fare_amt = 0.00;

You get the picture...

Cheers,

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.