Guest Deleted Posted September 21, 2013 Share Posted September 21, 2013 Sometimes I have a group of fields that I want to be unique. For instance, say I have dog_name, kennel_name and breeder_name. On their own, they don't have to be unique, but as a set, they must be. The only way I know to enforce this rule is to assign a single index to the three columns. Is this how I should be doing it? Confused? Here's examples. Okay: dog_name: Fluffykennel_name: River Hillsbreeder_name: amanda72 dog_name: Fluffykennel_name: Rivendell breeder_name: green_pastures Not Okay: dog_name: Fluffykennel_name: River Hillsbreeder_name: amanda72 dog_name: Fluffykennel_name: River Hillsbreeder_name: amanda72 Link to comment Share on other sites More sharing options...
Larry Posted September 23, 2013 Share Posted September 23, 2013 Yeah, you can just set a unique index on the combination of those three columns. Link to comment Share on other sites More sharing options...
Guest Deleted Posted September 24, 2013 Share Posted September 24, 2013 That's what I did. That's what I was wondering if it was right or wrong. It's okay? Link to comment Share on other sites More sharing options...
Antonio Conte Posted September 24, 2013 Share Posted September 24, 2013 It's ok and nothing really unusual to do. If it should be unique, add indexes as necassary. DBMS are incredible potent, and you don't really need to focus heavily on performance unless you work with really large data. A good tip is to focus on data integrity first, then focus on performance if things start going slow. The "correct" way here would be to work with IDs rather than names, but unless you need the three tables dogs, kennels and breeders, you don't really need to enforce that. The principle is called normalization, and teaches you how data should be split into the correct table entities. Good luck, Buttercream. Link to comment Share on other sites More sharing options...
Guest Deleted Posted September 24, 2013 Share Posted September 24, 2013 Hmm. Well I simplified everything for the sake of example. In my real database, it'd be more like this: dogs_table--------------- -dog_id-name-kennel_id-breeder_id -bunch of other dog columns like gender (enum, male or female) and altered (enum, yes or no) , basically, if it was something that had a 1 to 1 relationship with a dog, it went here --------- Anyhoo, another question. I have another table with a unique index that involves about 15 columns. Is that too much? Why so many columns? Well, the table keeps track of all the possible dog coat color combinations. You see, my image generator combines various layers to come up with all the colors and patterns that a dog can come in. I don't want it making the same variation twice. So it knows what it's made before, I use my dog_colors table to keep track of all the variations and to assign each one a color_id (int, auto_increment). It would be really bad if the same combination was added twice, so I created a unique index to stop this. Just about every column is part of the index. The only exceptions are color_id and color_name. Link to comment Share on other sites More sharing options...
Antonio Conte Posted September 24, 2013 Share Posted September 24, 2013 I'm mearly guessing here, but I think the index will really only slow down your INSERT / UPDATE queries, but not affect SELECT or DELETE. Slow inserts are often not really problematic — it's the select queries you want to go fast. Another guess would be that SELECT queries will actually be quicker due to your index, as indexes builds binary trees (b-trees) on your data. It's pretty much boils down to this: How does SELECT queries against the 15 columned uniquely indexed table compared to other queries to other tables? You'll get execution times in PHPMyAdmin. Larry might know more about this than me. Link to comment Share on other sites More sharing options...
Larry Posted September 25, 2013 Share Posted September 25, 2013 That seems excessive but it doesn't necessarily mean it's wrong. Link to comment Share on other sites More sharing options...
Recommended Posts