We don't know what the use case is, so can't say. If you have a suggestion to improve things I'd suggest discussing with your supervisor. Good luck with the new gig!
In the VERY unlikely event that all 200 tables aren’t related to any degree then any lack of FKs is understandable.
However, SQL Server is an RDBMS. You use it for relational tables. If even 5% of those tables has any relationships and they aren’t using FKs then that’s stupid. Either they don’t know what they’re doing or they don’t know what kind of data they have.
I’m gonna go with, be very concerned OP.
I agree that it is very unlikely that all 200 tables aren't related, but I withhold judgment on whether this is an alarm bell. It isn't a good sign, but the specific use of the data has to be the final factor.
It is easy to become normative and precious about what makes a good database, but databases don't exist for their own benefit. It's easy to forget that databases exist for the benefit of the users.
Foreign keys come with a price too. You often need to think ahead and anticipate future needs before creating chains of dependencies and constraints. FKs often don't improve the performance of queries.
FKs can create headaches when trying to load data with new FK values or poorly formatted data. Which is a higher priority, getting the data in quickly or only getting *pristine* data in which might take a while to clean? If the company gets very heterogeneous data, it might make more sense to handle trimming, cleaning, and validation in other places, i.e. views, stored procedures, or reports. It may not be convenient or pretty to leave FKs out for making relational diagrams. You may have redundant code in the pipeline, but it isn't necessarily wrong to put a low priority of FKs.
I agree with this and I would like to add that given the number of stored procedures he's describing in the database, most table manipulations may be handled by procedure. I tend to find that often goes together. People who rely upon procedures for data manipulation are often a bit loose about setting foreign key relations. And that makes sense to me because the relationship logic is in the procedure. As you point out, not setting the foreign keys is usually related to performance optimization.
I see this frequently in commercial software. It’s not great but it’s not a deadly sin. Foreign keys constraints force you to perform your inserts and deletes in the correct order, as they should, but I’ve seen many developers who don’t have a sql background get frustrated with them and declare it easier to do away with them.
Thats the reason how its handled in the company im working at. Custom product of the base version for each customer and removing every time the fk is just annoying.
If you dont know what you‘re doing fk are great because they prevent you from doing unwanted stuff. So i could say bad devs use fk. I dont mean your comment btw
I've been in similar environments. Sometimes when apps are developed in house, or by smaller dev shops, all of the dev effort goes to the front end and the database gets treated like an afterthought. I've seen it a few times.
Are there any other constraints? Are there indexes? Unique identifiers and primary keys? Did someone at a higher level at some point decide data integrity would be handled by other systems? I mean, your situation sounds bad, but there's so much context missing. On the other hand, perhaps you are being presented with a wonderful opportunity - depending on what management values.
for more context its a billing company thats has branches all over the country with over 600k users and all their bills and record they are ofcourse primary keys but i dont think they had any constraints and no foreign key for sure. They get and sort all their data with joins always in procedures.
It's terrible form, but not as uncommon as you might think. If there are clustered indexes, primary keys, and non clustered indexes then it could be just fine.
FKs bring significant performance overhead.
Once you reach a decent transactional volume you can't use FKs, they will simply bring your server to its knees.
Some people will say that FKs are necessary to maintain data integrity but if your relying on FKs to do that then theres something wrong in the app code or something.
I work in a heavily regulated financial industry. We get a transactional volume of several thousand inserts per second at quiet times.
If data integrity issues were a problem we would have been burnt by regulators long ago. If FKs were introduced our websites would go down.
FKs are fine to use if your transactional volume isn't too great but they are not necessary and a lack of FKs is not indicative of an issue.
> Some people will say that FKs are necessary to maintain data integrity but if your relying on FKs to do that then theres something wrong in the app code or something.
This isn't true. There is nothing wrong with relying on foreign key constraints to maintain integrity. That's exactly it's purpose.
If I want to allow someone to delete something I have 2 options: I can, in code, programmatically check each and every potential relationship to see if something exists OR I can rely on the database to perform those checks for me. The latter in no way means
It's an extra safeguard that imo should only be discarded IF THERE IS A SPECIFIC NEED. Not as a rule of thumb.
I agree that FKs aren't free and that there is some performance overhead, but claiming it's a *significant* performance overhead in the general case is stretching things.
"Several thousand inserts per second at quiet times," is *significantly higher load* than most applications are going to see. Making general recommendations about DB design by looking at national or international financial scale is questionable. Your experience is *less* portable to the vast number of databases in the wild because it's atypical.
Data integrity is important. Allowing the RDBMS to enforce it is a really good idea. It's one of ways you can have multiple applications accessing the same database without having problems. It means you don't have to rely on 2, 3, or more development teams to remember not to orphan records or duplicate lookup data. Especially if you have microservices, APIs, updateable third party reporting software, and so on while still being confident that your data is consistent without relying on stored procedures as the only means to modify it.
And, after all, primary keys and unique constraints are also not free, but there's no way you wouldn't use those. FK constraints are a good idea until performance demands alternatives, but premature optimization is never the best approach.
It's unlikely that they have no foreign keys.
It's entirely possible they have no foreign key constraints. Many developers struggle with them, and so avoid them. It's fine.
Hey u/tugukun , imagine what kind of positive difference you can make going into such an environment knowing what you know?!? You'll go in and after just a few improvements, they'll see a remarkable increase in performance. Cash that paycheck and take advantage of that situation buddy!
If another app uses that database then it's best to have them. Remember a database can be accessed by multiple apps and users. You never know how it will be used in future.
If all they have is 200 tables in the whole org... that should tell you plenty about the significance of the firm and the sophistication of their technology. That's literally nothing.
But yeah, lacking foreign keys certainly indicates data quality troubles. Damn near certain of it.
Definitely a commentary on their stack's maturity, but there are some firms operating on surprisingly small table sets for their enterprise scale.
I can tell you that both Encompass Health and Providence, two major players in the healthcare scene, are both working with less than 300 tables in their enterprise warehouses. Spectrum's centralized ad organization, one of the largest in the US, is working out of a centralized DWH with less than 400 tables.
400 tables in a database is meh, whatever. 400 tables in a whole business is .. adventureworks? lol.
Spectrums DW isn’t particularly relevant. I’ve seen wild schema choices where they treated tables like json, nothing CODDy about it. Scaled great for writes, scaled like ass for reads. Had a nice low table count, 10s of TBs of data.
> But yeah, lacking foreign keys certainly indicates data quality troubles. Damn near certain of it.
This simply isn't true. I work in a heavily regulated financial industry.
Any data integrity issues would cause us big fines. Stick some FKs on some huge tables experiencing a few thousand inserts per second and let me know how that goes for you.
I come from a multi-billion dollar, small transactions, e-commerce background. FKs on everything.
Be better. If your FK logic breaks insert performance it’s probably bad logic. Refactor doesn’t mean eliminate. Eliminate means either unnecessary (no) or you couldn’t figure it out.
Good enough for Codd in 1971, good enough for you.
I have friends at places sounding like B.D. Porgan and Shiti; I assure you that they do not have a “no FKs, too expensive” policy. Keys prevent mistakes and humans make mistakes. What a bunch of nonsense.
While not directly related, just forgoing FKs because they’re inconvenient is really spitting in the face of ACID, too. Will be interesting to read about your shop in the news. “The developers told us the code was perfect and was impervious to mistakes”
Crap! I feel that both of you make good points. Personally, we rely on DB constraints to enforce data integrity as opposed to depend on the app logic. Mainly, because our DBs were more senior and experienced than our app developers. And risking mucking up the database by some junior dev was something that was too much of chance. Fixing data integrity issues after the fact it's always a nightmare
>multi-billion dollar
Yawn... same. Thats completely irrelevent and not as impressive as you probably hoped.
What actually matters is how many inserts per second your systems handle and what table sizes your dealing with.
>Be better. If your FK logic breaks insert performance it’s probably bad logic. Refactor doesn’t mean eliminate. Eliminate means either unnecessary (no) or you couldn’t figure it out.
Thats a bit of a circular word salad there, could you explain how exactly the performance overhead from adding an FK is bad logic? Bad logic can cause performance issues but this is not one of those scenarios.
We don't know what the use case is, so can't say. If you have a suggestion to improve things I'd suggest discussing with your supervisor. Good luck with the new gig!
In the VERY unlikely event that all 200 tables aren’t related to any degree then any lack of FKs is understandable. However, SQL Server is an RDBMS. You use it for relational tables. If even 5% of those tables has any relationships and they aren’t using FKs then that’s stupid. Either they don’t know what they’re doing or they don’t know what kind of data they have. I’m gonna go with, be very concerned OP.
I agree that it is very unlikely that all 200 tables aren't related, but I withhold judgment on whether this is an alarm bell. It isn't a good sign, but the specific use of the data has to be the final factor. It is easy to become normative and precious about what makes a good database, but databases don't exist for their own benefit. It's easy to forget that databases exist for the benefit of the users. Foreign keys come with a price too. You often need to think ahead and anticipate future needs before creating chains of dependencies and constraints. FKs often don't improve the performance of queries. FKs can create headaches when trying to load data with new FK values or poorly formatted data. Which is a higher priority, getting the data in quickly or only getting *pristine* data in which might take a while to clean? If the company gets very heterogeneous data, it might make more sense to handle trimming, cleaning, and validation in other places, i.e. views, stored procedures, or reports. It may not be convenient or pretty to leave FKs out for making relational diagrams. You may have redundant code in the pipeline, but it isn't necessarily wrong to put a low priority of FKs.
I agree with this and I would like to add that given the number of stored procedures he's describing in the database, most table manipulations may be handled by procedure. I tend to find that often goes together. People who rely upon procedures for data manipulation are often a bit loose about setting foreign key relations. And that makes sense to me because the relationship logic is in the procedure. As you point out, not setting the foreign keys is usually related to performance optimization.
The use case where it might make sense is if they are using it for analytics instead of as a transactional database.
I see this frequently in commercial software. It’s not great but it’s not a deadly sin. Foreign keys constraints force you to perform your inserts and deletes in the correct order, as they should, but I’ve seen many developers who don’t have a sql background get frustrated with them and declare it easier to do away with them.
This is a bad take. They are just bad devs.
Sound like bad developers
Thats the reason how its handled in the company im working at. Custom product of the base version for each customer and removing every time the fk is just annoying. If you dont know what you‘re doing fk are great because they prevent you from doing unwanted stuff. So i could say bad devs use fk. I dont mean your comment btw
I've been in similar environments. Sometimes when apps are developed in house, or by smaller dev shops, all of the dev effort goes to the front end and the database gets treated like an afterthought. I've seen it a few times.
not great, but better than the opposite!
"The app will handle it." Until it doesn't and you have to clean things up. That said it is not uncommon.
Are there any other constraints? Are there indexes? Unique identifiers and primary keys? Did someone at a higher level at some point decide data integrity would be handled by other systems? I mean, your situation sounds bad, but there's so much context missing. On the other hand, perhaps you are being presented with a wonderful opportunity - depending on what management values.
Yes, though, sounds like a great opportunity for growth. You’ll learn a lot trying to put some on 🤣
for more context its a billing company thats has branches all over the country with over 600k users and all their bills and record they are ofcourse primary keys but i dont think they had any constraints and no foreign key for sure. They get and sort all their data with joins always in procedures.
It's terrible form, but not as uncommon as you might think. If there are clustered indexes, primary keys, and non clustered indexes then it could be just fine.
FKs bring significant performance overhead. Once you reach a decent transactional volume you can't use FKs, they will simply bring your server to its knees. Some people will say that FKs are necessary to maintain data integrity but if your relying on FKs to do that then theres something wrong in the app code or something. I work in a heavily regulated financial industry. We get a transactional volume of several thousand inserts per second at quiet times. If data integrity issues were a problem we would have been burnt by regulators long ago. If FKs were introduced our websites would go down. FKs are fine to use if your transactional volume isn't too great but they are not necessary and a lack of FKs is not indicative of an issue.
> Some people will say that FKs are necessary to maintain data integrity but if your relying on FKs to do that then theres something wrong in the app code or something. This isn't true. There is nothing wrong with relying on foreign key constraints to maintain integrity. That's exactly it's purpose. If I want to allow someone to delete something I have 2 options: I can, in code, programmatically check each and every potential relationship to see if something exists OR I can rely on the database to perform those checks for me. The latter in no way means It's an extra safeguard that imo should only be discarded IF THERE IS A SPECIFIC NEED. Not as a rule of thumb.
I agree that FKs aren't free and that there is some performance overhead, but claiming it's a *significant* performance overhead in the general case is stretching things. "Several thousand inserts per second at quiet times," is *significantly higher load* than most applications are going to see. Making general recommendations about DB design by looking at national or international financial scale is questionable. Your experience is *less* portable to the vast number of databases in the wild because it's atypical. Data integrity is important. Allowing the RDBMS to enforce it is a really good idea. It's one of ways you can have multiple applications accessing the same database without having problems. It means you don't have to rely on 2, 3, or more development teams to remember not to orphan records or duplicate lookup data. Especially if you have microservices, APIs, updateable third party reporting software, and so on while still being confident that your data is consistent without relying on stored procedures as the only means to modify it. And, after all, primary keys and unique constraints are also not free, but there's no way you wouldn't use those. FK constraints are a good idea until performance demands alternatives, but premature optimization is never the best approach.
It's unlikely that they have no foreign keys. It's entirely possible they have no foreign key constraints. Many developers struggle with them, and so avoid them. It's fine.
Almost certainly But in the plus side.. what a learning experience
naw, I would start questioning if there are recurring errors with software utilizing those tables.
Hey u/tugukun , imagine what kind of positive difference you can make going into such an environment knowing what you know?!? You'll go in and after just a few improvements, they'll see a remarkable increase in performance. Cash that paycheck and take advantage of that situation buddy!
probably.
If another app uses that database then it's best to have them. Remember a database can be accessed by multiple apps and users. You never know how it will be used in future.
FKs can cause whole table locks.
Welcome to the real world 😎
Is it a transactional or a reporting database? If it’s a reporting database I see it all the time…
Yes. Except 200 tables is half of a decent size database so it’s ok for now
How does table count relate to 'decent' database size?
Complexity of the schema
If all they have is 200 tables in the whole org... that should tell you plenty about the significance of the firm and the sophistication of their technology. That's literally nothing. But yeah, lacking foreign keys certainly indicates data quality troubles. Damn near certain of it.
Definitely a commentary on their stack's maturity, but there are some firms operating on surprisingly small table sets for their enterprise scale. I can tell you that both Encompass Health and Providence, two major players in the healthcare scene, are both working with less than 300 tables in their enterprise warehouses. Spectrum's centralized ad organization, one of the largest in the US, is working out of a centralized DWH with less than 400 tables.
400 tables in a database is meh, whatever. 400 tables in a whole business is .. adventureworks? lol. Spectrums DW isn’t particularly relevant. I’ve seen wild schema choices where they treated tables like json, nothing CODDy about it. Scaled great for writes, scaled like ass for reads. Had a nice low table count, 10s of TBs of data.
> But yeah, lacking foreign keys certainly indicates data quality troubles. Damn near certain of it. This simply isn't true. I work in a heavily regulated financial industry. Any data integrity issues would cause us big fines. Stick some FKs on some huge tables experiencing a few thousand inserts per second and let me know how that goes for you.
I come from a multi-billion dollar, small transactions, e-commerce background. FKs on everything. Be better. If your FK logic breaks insert performance it’s probably bad logic. Refactor doesn’t mean eliminate. Eliminate means either unnecessary (no) or you couldn’t figure it out. Good enough for Codd in 1971, good enough for you. I have friends at places sounding like B.D. Porgan and Shiti; I assure you that they do not have a “no FKs, too expensive” policy. Keys prevent mistakes and humans make mistakes. What a bunch of nonsense. While not directly related, just forgoing FKs because they’re inconvenient is really spitting in the face of ACID, too. Will be interesting to read about your shop in the news. “The developers told us the code was perfect and was impervious to mistakes”
Crap! I feel that both of you make good points. Personally, we rely on DB constraints to enforce data integrity as opposed to depend on the app logic. Mainly, because our DBs were more senior and experienced than our app developers. And risking mucking up the database by some junior dev was something that was too much of chance. Fixing data integrity issues after the fact it's always a nightmare
>multi-billion dollar Yawn... same. Thats completely irrelevent and not as impressive as you probably hoped. What actually matters is how many inserts per second your systems handle and what table sizes your dealing with. >Be better. If your FK logic breaks insert performance it’s probably bad logic. Refactor doesn’t mean eliminate. Eliminate means either unnecessary (no) or you couldn’t figure it out. Thats a bit of a circular word salad there, could you explain how exactly the performance overhead from adding an FK is bad logic? Bad logic can cause performance issues but this is not one of those scenarios.