Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a base table that defines parts and holds information like part number, description, price, weight, etc. We also have approximately 400 tables that reference the base table and provide additional information about the parts based on their type / category.

We started by using foreign key constraints so that a part cannot be deleted from the base table if it is being referenced in one of the 400 part specific tables but we quickly reached the maximum 253 recommended foreign keys for SQL Server 2005.

Are there any alternatives to foreign keys in this situation that will ensure data integrity? We haven't seen performance issues when accessing the data but updating an existing part in the base table will fail as the query plan is too complex.

share|improve this question
13  
Do you really think you need 400 part-specific tables? How different are each of these tables, really? I think you're trying to fix the wrong part of this design. –  Aaron Bertrand Mar 13 '13 at 14:30
2  
Roughly how many rows are you dealing with in this database? –  Jon Seigel Mar 13 '13 at 14:36
3  
I have to agree with Aaron Bertrand, if your design requires you to max out the foreign keys sql server supports, it may be time to consider a redesign. –  DForck42 Mar 13 '13 at 14:42
5  
I've done many designs in this area - pricing, product management, product specifications. Even in highly normalized designs, I've never come close to that many FKs on the same table. Are you perhaps doing some sort of data partitioning strategy (by client or time or something like that) that caused you to design in this direction? It's difficult to provide an answer without some more information about your design and design goals. –  Karen Lopez Mar 13 '13 at 16:43
2  
Could you provide schema example for say 5 of these tables ... –  Damir Sudarevic Mar 15 '13 at 19:57
show 3 more comments

3 Answers

If there's any way to group parts, you might be able to introduce intermediate tables as a workaround. This won't work.

Parts
+ Table 1
+ Table 2
+ ...
+ Table 400

But something along these lines might.

Parts
+ RedOrangeYellow parts
  + Table 1
  + Table 2
  + ...
  + Table 200

+ GreenBlueIndigoViolet parts
  + Table 201
  + Table 202
  + ...
  + Table 400

I'd want to take a hard look at your DDL before I recommended doing this, though. And if you do this, don't start throwing ID numbers all over the place. You ought to be able to join "Table 400" directly to "Parts" without including "GreenBlueIndigoViolet parts".

share|improve this answer
add comment

If you truly cannot combine tables, why not create a TRIGGER on the base table to prevent deletion?

Start here: http://msdn.microsoft.com/en-us/library/ms189799(v=sql.90).aspx

share|improve this answer
add comment

Replace the 400+ tables with one. Only need 3 fields (+1 autonumber or whatever primary key if you want it, don't have to have it you can form it out of the other fields)

Item ID Attribute Value

So where in your other tables each field represents an attribute, in this table your attributes are all in one field. You would have something like this

ItemID Attribute Value

Sock Material Wool

Sock Color Red

Sock Weight 20 pounds

Alien Planet Alpha Centauri

Alien Color Purple

Alien Friendly No

The ItemIDs should probably be a number/alphanumeric ofc. Then, you just crosstab w/ Atrributes as column headers when necessary to generate the tables like you like. This also allows for better queries for things like "Show me all items that are from Alpha Centauri" which could give you back the Alien and also the Meteorite fragment that contained the plague that wipes out mankind (it's coming.....)

Optimization may be tricky depending on how many records there are but it's a much better way to design this. I did the same for a database that contained a bunch of recipes (10k+) that had few overlaps. Worked well in that case. Didn't have any speed problems really. Yours may be harder depending on how many you are dealing with.

share|improve this answer
3  
This is called Entity Attribute Value model and it's normally a pretty terrible idea for a lot of reasons. For your example of the "alpha centauri" scenario, you would be looking at probably 2 table scans, and indexes can't be used. Also it nullifies any advantage to data types, makes relational constraints impossible, and is generally not optimizable or scalable to any degree. If you need to store more than a hundred or so rows like this, don't. –  JNK Mar 15 '13 at 19:08
1  
As @JNK points, it's usually not a good solution. And even worse when you do it with only one table. I'd say the minimum is 3 tables (Entity, Entity_Attribute, Entity_Attribute_Value). If you want to add semi-proper treatment for different datatypes and referential constraints, you'll need more. –  ypercube Mar 15 '13 at 19:12
1  
While the OP may be approaching some kind of limit with traditional normalized design due to the sheer complexity of the entities in their system, most extremely complex systems are not really handled any better in EAV, since it offers much weaker type support and application-level referential integrity since the data is all stored fairly generically. EAV has its useful place, but I wouldn't be ready to recommend it as an answer for this problem as currently stated. –  Cade Roux Mar 15 '13 at 19:22
    
Huh, learn something new every day. I'm self-taught and that was somethign i came up with to solve the recipe problem I was having, which sounds similar to this guys issue. Ah well. Thanks for the info on that, I'll look into EAV, maybe there's a cleaner way to solve my problem as well. Could be it worked easier for me since all the values were the same datatype. I dunno. –  user2125055 Mar 15 '13 at 20:16
2  
@user2125055 It's no problem, and don't take any of the criticism personally. We are just downvoting the idea of using EAV! There are definitely use cases where it makes sense, but because of the drawbacks you need to be very careful with it. –  JNK Mar 15 '13 at 20:53
show 4 more comments

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.