SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Ensuring Designs have Flexibility


Ensuring Designs have Flexibility

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)

Group: Administrators
Points: 651189 Visits: 21472
Comments posted to this topic are about the item Ensuring Designs have Flexibility

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Dalkeith
Dalkeith
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3261 Visits: 1433
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.
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9458 Visits: 2028

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

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


RonKyle
RonKyle
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28978 Visits: 4603
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.




trashncarry
trashncarry
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 62
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).
Aaron Cutshall
Aaron Cutshall
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8031 Visits: 1344
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.


...when ye are in the service of your fellow beings ye are only in the service of your God. -- Mosiah 2:17
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117275 Visits: 15446
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
patrickmcginnis59 10839
patrickmcginnis59 10839
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21444 Visits: 8601

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?





to properly post on a forum:http://www.sqlservercentral.com/articles/61537/
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9458 Visits: 2028
patrickmcginnis59 10839 - Friday, January 18, 2019 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?




Among other things. Smile

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?
patrickmcginnis59 10839
patrickmcginnis59 10839
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21444 Visits: 8601
roger.plowman - Friday, January 18, 2019 11:00 AM

Among other things. Smile

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?


to properly post on a forum:http://www.sqlservercentral.com/articles/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search