January 16, 2019 at 9:04 pm
Comments posted to this topic are about the item Ensuring Designs have Flexibility
January 17, 2019 at 2:18 am
I am struggling with this right at the moment - I am attempting to build a national multi-tenant site. Important in this is allowing the flexibility in lookups. Some lookup tables will relate to everyone while others will need to be specific to the individual's organization.
I have already had to re-start the front end from scratch as after changes to the database structure to improve multi-tenancy it broke the front end so badly it was easier to redesign from start. For my main tables for unusual things I will be using entity attribute value sub tables but control it a bit by making the attribute field a lookup albeit that users will be able to add to that lookup.
cloudydatablog.net
January 17, 2019 at 7:02 am
Rule 1: Dr. House's rule of users: Everybody lies.
Rule 2: Constants aren't.
Rule 3: Even laws of nature aren't guaranteed to stay the same
Rule 4: EAV sucks, but sometimes it is the only solution
Rule 5: Rule 4 isn't acceptable
So, bottom line? Go meta. Instead of modelling say, an invoice or an employee look deeper. What is an employee? What assumptions are built into that word?
For example, employee assumes they are employed, and the unspoken assumption is by the user's company. Of course that leaves out consultants. And customer employees that may collocate on your site. That still need to use your system and be tracked...
And what about amount fields? US-centric companies generally only deal with dollars. But what happens if you have to deal with specific denominations? Coins vs bills vs gift cards vs counted cards vs postage stamps? Stamps are particularly egregious because their value varies from year to year so you have to store the per-stamp amount as well as the total value of all stamps for each transaction.
And there's an even more basic assumption with money. US currency has fixed ratios to other denominations, especially the penny.
However, this fixed relationship goes away when you add Euros and Yen and the hundreds of other currencies used in the world. Dealing with currency valuations between currencies is an entire industry in itself!
Then, of course, there's the problem of complexity and the headaches that brings. As in you don't want any.
The front-end developer's answer is "oh, the app can handle it. Don't worry about building the rules into the database, we don't need no stinkin' ACID compliance."
The DBA promptly explodes. 🙂
I once built a database using the assumption that 255 denominations would be plenty for a US-only database. After all, our company would never do business outside the US so surely 255 denominations was plenty? After all, there were only 16 denominations and that's counting stamps. (Oh, stamps...sigh).
Yeah, no. Somebody deep in the heart of one of our customer sales departments said "Let's get ATMs to dispense gift cards! We'll let the machine dispense a bunch of different company's gift cards in various fixed denominations!" And then somebody else said "Sure, but let's make sure we offer programmable amounts too!"
You can see where this is going...
Easy fix, right? Just change the denomination key to 2 bytes. In every single table that uses denominations. While in flight.
Not fun.
That lesson was painful. I had been assured by everyone years ago that we'd never have more than maybe 20 denominations. Hah!
Dr. House was right, right about everything. :laugh:
January 17, 2019 at 7:20 am
Buildingand designing a database isn't that hard
I used to think this. In one sense it's true, it isn't hard to design and build a database. But it is apparently hard to design and build a good one. I have seen too many deficient OLTP and OLAP designs, and the deficiencies caused issues of one type or another.
January 17, 2019 at 7:57 am
I don't get it. It is a piece of cake to update the database. I develop a multi tenant AR Collections product. I have 45 Multi tenant Azure SQL Databases containing data for 150 customer on 3 datacenters around the world. I drop DB Updates approx. every month to 2 months depending on what all needs changed. I have a "relatively" automated process for doing the updates. I also use the same update process for the 12 accounts that are premise based (housed on SQL Servers within a customers network that I have no access to). 1 key is to not deprecate anything (no deleting columns, stored procs, etc), but it is not hard to do. I maintain my DB objects in a Visual Studio DB Project, using it basically as source control, and then compare it against a live DB to know/decide what I need to put into my update scripts. I have been doing this since Azure came out (we were a very early adopter), and have been using a same basic process for many years (before SQL 6.0).
January 17, 2019 at 8:27 am
With Agile's focus on the current sprint, too many folks do not look ahead especially when it comes to database design. Looking forward down the road and incorporating flexibility in the database design goes a long way to making projects smoother. Also, making database changes are not as hard as it is to get developers and DBAs properly communicating. Of course, circumstances and plans change but that should be expected and anticipated. We all need to get our heads out of our task at hand, look around, communicate with each other, and plan for the future in order to develop solutions that not only meet expectations but exceed them.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
January 17, 2019 at 9:47 am
It's impossible for IT to create a database model that lines up with the organization's business model and user requirements, when the business model and user base are constantly changing or simply not known by the organization itself.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 18, 2019 at 10:10 am
The front-end developer's answer is "oh, the app can handle it. Don't worry about building the rules into the database, we don't need no stinkin' ACID compliance."
The DBA promptly explodes.
Having business rules in the app might be the way to go though for all we know. What part of ACID do you think they're breaking? The C?
January 18, 2019 at 11:00 am
patrickmcginnis59 10839 - Friday, January 18, 2019 10:10 AMThe front-end developer's answer is "oh, the app can handle it. Don't worry about building the rules into the database, we don't need no stinkin' ACID compliance."
The DBA promptly explodes.
Having business rules in the app might be the way to go though for all we know. What part of ACID do you think they're breaking? The C?
Among other things. 🙂
Concerning business rules, they should be in the DB so no one can get around them. That's rather the point of having enforceable rules, right?
January 18, 2019 at 11:19 am
roger.plowman - Friday, January 18, 2019 11:00 AMAmong other things. 🙂Concerning business rules, they should be in the DB so no one can get around them. That's rather the point of having enforceable rules, right?
I guess you're saying that the programmers who write T-SQL can prevent the rules from being broken, whereas the programmers who write app code, cannot?
January 18, 2019 at 11:29 am
Concerning business rules, they should be in the DB so no one can get around them. That's rather the point of having enforceable rules, right?
Business rules should be in the middle tier. It's possible to have several instances of those. The database is a chokepoint. Processing them there is very detrimental to performance.
January 18, 2019 at 11:38 am
roger.plowman - Friday, January 18, 2019 11:00 AMpatrickmcginnis59 10839 - Friday, January 18, 2019 10:10 AMThe front-end developer's answer is "oh, the app can handle it. Don't worry about building the rules into the database, we don't need no stinkin' ACID compliance."
The DBA promptly explodes.
Having business rules in the app might be the way to go though for all we know. What part of ACID do you think they're breaking? The C?
Among other things. 🙂
Concerning business rules, they should be in the DB so no one can get around them. That's rather the point of having enforceable rules, right?
That entirely depends on the business rule so as usual the statement, it depends applies.
For example order numbers being unique, well that makes a lot of sense to have a rule in the DB for, on the other hand an order with product A on it can not also have product B on it that makes almost no sense to have in the DB.
January 18, 2019 at 11:48 am
patrickmcginnis59 10839 - Friday, January 18, 2019 11:19 AMroger.plowman - Friday, January 18, 2019 11:00 AMpatrickmcginnis59 10839 - Friday, January 18, 2019 10:10 AMThe front-end developer's answer is "oh, the app can handle it. Don't worry about building the rules into the database, we don't need no stinkin' ACID compliance."
The DBA promptly explodes.
Having business rules in the app might be the way to go though for all we know. What part of ACID do you think they're breaking? The C?
Among other things. 🙂
Concerning business rules, they should be in the DB so no one can get around them. That's rather the point of having enforceable rules, right?
I guess you're saying that the programmers who write T-SQL can prevent the rules from being broken, whereas the programmers who write app code, cannot?
No, of course not. But there is one critical difference between rules in the database and rules in the app.
App rules only apply for that one application. If someone else writes a second application and deliberately (or accidently or ignorantly) doesn't include the rules, then the rules go bye-bye. And long with it consistency.
If the database enforces the rules applications can not bypass them. One app or a thousand, all hitting the same database means all of the apps must abide by the rules.
Security 101, segregation of responsibility.
January 18, 2019 at 11:54 am
RonKyle - Friday, January 18, 2019 11:29 AMConcerning business rules, they should be in the DB so no one can get around them. That's rather the point of having enforceable rules, right?
Business rules should be in the middle tier. It's possible to have several instances of those. The database is a chokepoint. Processing them there is very detrimental to performance.
I think there can be advantages to programming in the database layer, especially if you can afford any costs that arise taking this approach. Programming in the application layer will obviously have costs and benefits too. Paradigms and toolsets vary, sometimes you might even need to take into consideration what sort of actual constraints the design spec imposes and the resources available to get the work done.
January 18, 2019 at 12:03 pm
roger.plowman - Friday, January 18, 2019 11:48 AMNo, of course not. But there is one critical difference between rules in the database and rules in the app.App rules only apply for that one application. If someone else writes a second application and deliberately (or accidently or ignorantly) doesn't include the rules, then the rules go bye-bye. And long with it consistency.
If the database enforces the rules applications can not bypass them. One app or a thousand, all hitting the same database means all of the apps must abide by the rules.
Security 101, segregation of responsibility.
Well yeah if your shop is the wild west heh I hear ya man, put the sheriff on that server and lock the doors!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply