8

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.

Improve this question
8
  • 14
    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. Commented Mar 13, 2013 at 14:30
  • 2
    Roughly how many rows are you dealing with in this database? Commented Mar 13, 2013 at 14:36
  • 4
    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. Commented Mar 13, 2013 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. Commented Mar 13, 2013 at 16:43
  • 4
    Could you provide schema example for say 5 of these tables ... Commented Mar 15, 2013 at 19:57

3 Answers 3

Reset to default
6

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".

Improve this answer
4

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

Improve this answer
-4

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.

Improve this answer
9
  • 4
    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. Commented Mar 15, 2013 at 19:08
  • 2
    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. Commented Mar 15, 2013 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. Commented Mar 15, 2013 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. Commented Mar 15, 2013 at 20:16
  • 3
    @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. Commented Mar 15, 2013 at 20:53

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.