Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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 Aug 13 '11 at 21:43
    
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 Aug 17 '11 at 0:59
    
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 Aug 17 '11 at 20:02
    
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 Aug 19 '11 at 22:16
    
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 Aug 19 '11 at 22:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.