0
I'm trying to normalize a very simple dataset:
user_id | role_id
I have a table with 60,000 records, and all I need to do is turn this into a join, so one table contains users, and another table contains roles, with this table becoming a simple join.
The Table Analyzer Wizard should handle this perfectly, but when I come to the step of clicking Finish, I get the error 'Cannot define field more than once', and it fails. Any suggestions on how to either get the wizard to work properly, or a different way to get this table normalized?
Myrddin Emrys
Posted 2011-08-11T18:47:18.403
Reputation: 2 122
Surely you can just run two queries to create the two tables you're going to join with the DISTINCT values from each of the two tables? – David W. Fenton – 2011-08-13T21:43:44.650
I can, and did in the end, but that does not make this bug any less frustrating. The full normalization I needed to do was actually more complicated than two columns, but I was able to replicate the issue with this simpler dataset. – Myrddin Emrys – 2011-08-17T00:59:05.467
This is one of those things that is so simple I can't imagine why one would use the Table Analyzer Wizard to do it. If find that much too often, that wizard guesses wrongly about how I want things restructured, so it's not of much use in the first place. – David W. Fenton – 2011-08-17T20:02:17.157
The analysis wizard allows you to choose your own structure; it's not like I allow it to do the deciding. However, when it works, it is a much faster way of splitting the normalized data out and leaving FKs behind. In other words, the wizard makes splitting tables easier, even when you're doing all the deciding of what to split and how. – Myrddin Emrys – 2011-08-19T22:16:56.867
Well, YMMV -- I've never found it saves any time at all, but then, I've been doing normalization for a long, long time, and can do it more efficiently than the wizard. I can't see that complaining that a general-purpose tool sometimes fails to make the right decisions gets you very far, though. It's to be expected, seems to me, and in that case, you just do the task manually. – David W. Fenton – 2011-08-19T22:21:07.427
But it's not about failing to make the right decision... it's about taking a table, manually splitting out the columns, and it fails to link the tables properly. There are no decisions it's making, it's simply (failing to) creating the new indexes. – Myrddin Emrys – 2011-08-25T15:22:28.540
It's a wizard. It's guessing what you need to have done, and it's just a piece of code, so it won't get it right every time. This seems self-evident to me, and not something to complain about. – David W. Fenton – 2011-08-26T21:11:57.217