Do large systems use foreign keys in their databases? [closed]

Asked 19/1, 2012 at 14:54 Answered 19/1, 2012 at 15:13

sql foreign-keys scalability rdbms production

One of the disadvantages people point to with using foreign keys in a relational database is the overhead of making sure the parent table exists before doing any insert operation. (Example). As your database and operation grows, this effect is amplified. Does anyone know if large sites on the web use them then? If so, how do they get around that extra overhead? If not, as your development team gets bigger, it seems that that could cause a lot of potential bugs/conflicts/miscommunication/orphan-rows.

Any insight?

Formyl answered 19/1, 2012 at 14:54 Comment(7)

Overhead vs Ensuring Data Validity - unforutnately a bit subjective, but will be interesting to see what answers you get. I would of thought its a more of a CW than a question though. – Karykaryl 19/1, 2012 at 14:57

Many "large sites on the web" do not use relational databases at all. – Slipway 19/1, 2012 at 14:57

@Slipway - Umm, yeah they do. Not all, probably not for HTML content, but for transactional data they often do. – Reina 19/1, 2012 at 14:59

Please don't close this! I really want to hear the answer. – Downturn 19/1, 2012 at 14:59

@CarlBenson - If

as your development team gets bigger, it seems that that could cause a lot of potential bugs/conflicts/miscommunication/orphan-rows.

is a problem, your code base in any language will also be at risk. That's why you have processes. – Reina 19/1, 2012 at 15:0

I guess it depends on what do you mean with "large sites". – Bastien 19/1, 2012 at 15:0

@Dems Many do, many don't. I'm just saying its not the only type of database that exists to handle huge data. – Slipway 19/1, 2012 at 15:0

I work for a telecom, yes they do, often the cost of FK is too small compared to severity one major incidents (hours of time lost investigating, patching ...) that can be caused by accidents due to lack of FK checking.

Coth answered 19/1, 2012 at 15:13 Comment(0)

This depends on your definition of "large site": If for example Citigroup online bank is a "large site", I guess they do.

What I mean by this example is, that the use (or not use) of foreign keys, access by stored-procedures-only etc, depends on the requirements of the business logic (and maybe its legal framework) more than on technicalities only.

Niobous answered 19/1, 2012 at 15:4 Comment(2)

To clarify, my emphasis is on a hypothetical website that has a huge amount of data writing to and reading from a RDBMS. Citigroup would be a "large site" to the extent that these FK overhead costs would actually affect them. Maybe Twitter or Wordpress are better examples. – Formyl 19/1, 2012 at 15:15

Twitter should not (and AFAIK does not) use a RDBMS and thus no foreign keys, Wordpress doesn't use foreign keys - exactly my argument: The business logic and legal framework do not require it. Citibank will use them, even if the overhead affects them, but the business logic and legal necessitate it. – Niobous 19/1, 2012 at 15:20

Recommended topics

Hot tags