2

What are the pros and cons of breaking out optional 1:1 attributes into their own separate model.

For example, I just encountered Rails code like this:

class Dogs << ActiveRecord::Base
  # :id (pk), :breed, :weight, :height, :tail_length
end

class DogSpotsInfo << ActiveRecord::Base
  # :dog_id (pk), :spot_color, :avg_spot_size, :num_spots
end

But this is how I would have done it (leaving the spot fields null as necessary):

class Dogs << ActiveRecord::Base
  # :id, :breed, :weight, :height, :tail_length, :spot_color, :avg_spot_size, :num_spots
end

At the DB level, I believe the only difference is queries involving the optional attributes will require another join?

Are there any other cons to the former approach? Are there any pros?

EDIT:

I guess on a massive dogs table where say 20% of dogs have spots, maybe one pro the former approach is faster sequential scans but I'm not 100% sure about that, and if that's the only pro it seems like premature optimization.

Another pro I can think of is it keeps the models smaller and neater. But if that is the goal, perhaps could you do it without affecting the DB structure, by having something like has_spots :spot ? What is the best practice here?

Improve this question
1
  • Personally I'm not a fan of ActiveRecord's has_one association type. It only makes sense if a subset of your fields is subject to some constraint (e.g. incompatible/disallowed combinations of column values). People shouldn't be afraid of wide tables in the database, provided they're properly indexed. Commented Sep 26, 2012 at 19:56

4 Answers 4

Reset to default
2
+50

Including all the records in one table is likely the preferential way to go. It gives you better performance at the cost of size.

The performance is arguable depending on how the tables are actually used. If the systems expensive queries doesn't require spots data (like a mass listing), it could potentially be faster not having them on the table, but if you kept the data on table tailoring your query to only pull in the needed data would eliminate the cost of the query almost entirely.

I've seen the 1:1 releationship implemented because it makes more sense to a developer than any logical difference in code. Left Outer Joins are more expensive than Inner Joins and Joins are more expensive than no joins.

You could potentially decrease the overall database size by not keeping this information on table, but for most small applications (under 1 TB) you should be optimizing for performance.

The only real situation that I would think that this set up would be ideal is if you wanted to constrain the type of spots a user can choose from or if the you did a lot of processing on these elements of a dog separately.

You can get the same constraining feel by using a lookup table of all possible values and only allowed the user to select from values in the lookup table. Then go back and insert raw values in the dog table. This does does have the side effect of potentially polluting the dog table with data not properly scrubbed by the web app, but it would be a quick effort to isolate what data is not inside of the constraints by comparing to a spot look up table and correcting the issue in the web app.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Sounds interesting. May I ask how much experience you have in this field (just to know the weight behind you words)?
I've worked with and was trained by guys who used to work for a major warehousing company. (They housed a couple hundred terrabytes of information about people.) I got most of my performance bias from them. I've puruse some of the Kimball books on dimensional design in my free time, have been the sole resource on schema designed. Refactored queries to their leanest is one of my strongest points. I've been working with databases for three years now (I know not very long), but it's been a very immersive experience so far. So, I'd take it with a grain of salt, but I still think it's solid advise.
2

1:1 relationships are frequently decomposed when the relationship is of the "Is-A" type rather than of the "Has-A" type. In the extended ER model this is called "specialization". In the world of SQL tables, this sometimes goes by the name "Class Table Inheritance". You can look either of these terms up for a pretty good treatment of the corresponding subject.

Class Table Inheritance contrasts with "Single Table Inheritance" which results in a single table with NULLS in places where a value would be irrelevant. This looks like your choice for Dogs and Spots.

In the world of "Cars", a specialization might be a table for "Vehicles", one for "Autos" and one for "Trucks". ("Lorries" are called "Trucks" on my side of the pond.) Autos and Trucks are specialized types of Vehicles. The attributes that are stored in trucks and not in vehicles are attributes that are irrelevant to vehicles that are not trucks.

The purpose is generally not to improve performance, but to improve the form of queries. Queries that are about truck data only can query the trucks table. Queries that are about vehicle data only can query the vehicles table. And queries that involve data about both vehicles and trucks can query a view that joins vehicles and trucks over a common column.

Contrary to what others have opined, narrow tables do outperform wide tables, although the effect is minor compared to indexing and joins.

You can combine Class Table Inheritance with a technique called "Shared Primary Key", which you can look up. You get very fast, and very easy joins compared to other ways of relating the tables to each other. Shared Primary keys involve more work at insert time, because you have to propagate the common value from the generalized table to the appropriate specialized table under program control.

If I were doing your case, I would look for ways to exploit specialization around the various fuel types you mentioned. Some of your queries might end up scanning only one of five specialized tables (aka "subclass tables") thereby running five times as fast.

Improve this answer

Comments

0

The former approach is better any day then the latter one if you have a huge amount of databases.

It is always a plus point to separate the things in a separate table in which we are not sure the value will come or not. like in your case dog and dog spot if only 20 % of dogs have spots then their is no need of keeping it in the same database table as 80 % of the columns will be nill and while retrieving data from the main table a lot of data will be picked which will surely hamper the server performance. If the database is small then perhaps one could do not neccesarily make a second table

If we are worried of the time complexity of joins and all then we can apply indexing which will further decrease our cost.

so the main point

1) if database contains many records apply former technique plus do some indexing to save the server cost

2) If database is small u can go for the latter one

Improve this answer

Comments

0

Pros of de-normalization:

  • Each table takes up memory, less tables means less overhead (once I was dealing with a Database with 30K tables with a total of 100 meg of data, and the database memory usage was enormous)

  • Manual Analysis can be done easily, i.e export 1 table to Excel, Google Spreadsheets or whatever and you use filters

Cons

  • Lots of columns with null values.

Another approach to balance lots of columns vs extra attribute tables is to use serialized arrays for elements that are less common, like iPod interation, and then use a Search server (elasticsearch, websolr and so on), which can handle the queries with full text... This would allow you to add new elements to Cars table without new columns. Like Self Driving Car, Self Parking Car.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.