I have 5 tables as seen in the pic. I am doing data migration from access to mysql and have to take these 5 tables down to 3. Basically I now have 3 tables but I am being told that they are not producing the correct results.

The pic I have included is what the table design looked like before I moved any data. I figured I would start there as maybe I made a mistake when I joined the tables to get the data to copy over to the new tables.

I used left outer joins to do this. This would be the DML I used to do it with. I'd like to know if this was correct. The requirement was to show all rows on both sides of the two join tables. Once all rows were present then I would begin to consolidate and move the data into the three new tables.

Tables 4 and 5 are the join tables and will join tables 1, 2 and 3. The joins seem to produce the correct results but I'd like to be sure.

your joins look okay, at least in basic structure, although as soon as you started doing crap like table4 AS t2 and table2 AS t3 i quickly became disoriented and stopped reading the SQL

how in the world are you going to collapse these into 3 tables -- it's just not possible!!

Hey Rudy, thanks so much for the help. I agree with you about the DML being confusing. I'm sorry for modeling it that way but I was in a rush. I made the ERD and DML a bit different this time so it’s easier to follow.

Just a little background on this project. This is a state project and I was forbidden to post any actual ERD or DML on a public forum so I made this mockup of what I'm dealing with. The managers want to migrate from access to mysql and have taken a fairly decent design IMHO and denormalized it for no reason at all. I am tasked with condensing tables 1 and 3 and deleting table2 all together. Then I need to use a join table to join to table5. There will only be a single one to many join at that point.

I figured that what I would do is join up all of these tables the way they were in access and after I got the correct results I would take the columns I wanted and move them into a temp table and just start moving the data that way. I have 87 tables to do like this.

One of the problems that I see is that the managers don't know anything about databases and are expecting a different result set than what my joins are currently producing. I matched my rows up to the access database and they are identical. Again, the problem is that they are not sure what they really want it seems and consequently are making me question or second guess myself. I figured I would run it by you or Pat and see what you though of this. ....Aside from it being a "cluster" of course.

What I was told was that there is only only one column from table1 that is needed and that should be brought into table3. Every other column fron table 1 can be deleted. However, everything else in tables 3 4 and 5 is being used.

Question.. Would I get the same result set if I changed the order of the tables when doing my left joins? Does the table order matter? I read in your book that a left join returns all rows from the left table but I am a bit confused how that should work when there are join tables involved or does this even matter?

Just a side note.. There is a proxy set up at work so I won't be able to respond to this thread until after I get home. I can't access anything "outside" of what the state says we can.

"cluster" is a pretty good way of describing the situation

if, as you say, the managers don't know anything about databases, why are they presuming to dictate which tables and fields you should have?

if i were you, i would walk away from this project

again, your query looks okay, but regarding left joins, try running the query with inner joins and see what happens

Based on the ERD you presented, you need to use all INNER JOIN operations, no LEFT JOINS at all. The reason for this is that all of the relationships are one-to-many, none are optional. There are no entities with zero cardinality according to your ERD, so you don't need any outer joins.

For what it is worth, you'll get a simpler plan if you join them as 4 then 5, then 1, 2, and 3 in any convenient order. This could execute a smidgeon faster too, especially if you're using Inno-DB instead of My-ISAM.

-PatP

i'm going to go ahead and guess that the relationships actually might be optional, but this nuance was not reflected properly in the ERD

Yeah, every day that goes by the cluster factor seems more and more apparent to me.

The managers know just enough about databases to make them dangerous. Unfortunately they don't even really know what result set they want. I spent more than an hour today with both managers that seem to contradict what the "right" result set is. One says it should be one way and the other says something different. Then they somehow seem to agree and I don’t follow their logic so I’m lost.

This project is an absolute nightmare and if I had another project that would pay me I'd leave it in a heartbeat. I have no idea how I'm ever going to collapse these tables, as you already said.

Yeah, the inner joins produce only those rows where there is /are matching record(s) on the other side of the join table. I was told though that they wanted to see all unmatched records as well which is why I used left joins.

Let me ask this… Is it possible to take a field out of table1 and append it along with its corresponding data into table 3 and delete table 2?

How I thought I would do this would be to left join the first 3 tables to get my results and then create a temp table to copy the results over to. Once the results are in the temp table I could then join the temp table to tables 4 and 5. Is this possible?

I think you're right Rudy, they are optional.

Hey Pat, thanks for the input and direction. Actually, the ERD that I have in my possession from the state doesn't specify the cardinality either but because I have been working with the data, I can attest to the fact that ALL of these tables have optional cardinality. Tables 1 3 and 5 can have data in them with no data on the other side. This is what seems to be causing the whole misunderstanding between what they want to see.

For what it's worth, the entire new system will be using MyISAM in a multi-user environment.

possibly

but that description of the objective is so vague that i cannot offer any further advice

in general, you can not collapse a many-to-many relationship (table1 to table3 via the relationship in table2) without some sort of denomalizing compromise, e.g. a comma-delimited list of values in a single new column

Unfortunately the abstraction is killing us. We're to the point where what you're asking borders on: "What happens if I turn that thing in the car a couple of times?" Heck, you could turn left, be deafened by the radio, turn down the air conditioning, or throw it into four wheel drive.

If the managers can't agree on what they want, then the problem lies in inadequate specfications. The architect stopped at too abstract a level, so that the developers/managers can imagine whatever they need or want. It sounds like they've ordered a "vehicle" and are waiting for you to provde them with one, without specifying what it needs to move or where it needs to work. There is considerable difference between a bicycle and an airplane even thogh the Wright brothers did both!

Until you get a more solid specification that you can understand, you can't possibly deliver. The technical details are just details if you don't understand the problem to be solved.

-PatP

Thanks for all the help guys.

You're right Pat, I don't understand the problem to be solved. I'm working with a 19 year old kid who doesn't like to share details about how this system is supposed to work. I don't really think he fully understands either.