February 8, 2022 at 2:16 pm
We are trying to get requirements from the development group who has just started programming. We are creating a spreadsheet to send out to all of the dev groups asking for the data elements that they will need to be read and written to and from the database(s).
Right now the spreadsheet is crude with 3 columns: data element name, description, and example data.
I would like to expand on the spreadsheet so that we get the most meaningful info from the groups. What is the best way to go about this interview process? Seems like as of now, the dev groups are expecting us to tell them what data they need. I'd like to create a thorough form where it is clear to each group what they need to document on the spreadsheet.
Any ideas ? Or a good example doc that people have used in the past?
Thanks..
February 8, 2022 at 3:28 pm
When doing a database design, simply getting a list of columns is not how I'd pursue it. Instead, I want to understand what it is that they're attempting to store in the database. Then, how are those things interrelated. Is that relation one to one, one to many, many to one, many to many? Yeah, you also probably need data types, fixed lengths, variable lengths, and all that. But it's not something you put into a spreadsheet. It's something you put into a data model, logical then physical, but at the very least, physical. It's all about understanding the business and application needs, not just getting a list of columns.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2022 at 8:33 pm
We are trying to get requirements from the development group who has just started programming. We are creating a spreadsheet to send out to all of the dev groups asking for the data elements that they will need to be read and written to and from the database(s).
Does that mean that the database(s) already exist?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2022 at 1:44 am
No the database does not exist. I have a conceptual design doc where i have defined several entities based on the UI designs i have seen. I will be elaborating on this doc. But now coding for the designs is starting and we want to make sure they provide some specification. so we are trying to be proactive and give them a form that Tells us what they need to store in the db
February 9, 2022 at 2:45 am
No the database does not exist. I have a conceptual design doc where i have defined several entities based on the UI designs i have seen. I will be elaborating on this doc. But now coding for the designs is starting and we want to make sure they provide some specification. so we are trying to be proactive and give them a form that Tells us what they need to store in the db
That actually sounds pretty good.
On your "evaluation" spreadsheet, you might want to add a couple of columns for things like if the column needs to contain unique data and whether or not it should be a PK or AK (either would indicate that need for the column to be unique). You might need to add some for if there needs to be a Default or Check constraint including a dependency on the content of another table (FK).
You might also want to consider forcing any MAX or XML datatypes out of row, making them NOT NULL, and defaulting them to an empty string so that when they are populated, neither the data nor the pointer will constitute an "ExpAnsive" update that will cause massive page splitting and the resulting fragmentation.
If you use "poor man's auditing" where you have a Modified_BY column, consider that those will also constitute "ExpAnsive" updates if they're VARCHAR() in nature and consider methods to use INTs instead of actual procedure or human names.
I have to admit that I'm a git jealous... it's not often that one gets to build a database from the git. It's a really good chance to do it right and that chance doesn't avail itself often.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2022 at 1:57 pm
Based on this, yep, I'm even more positive the right way to go is with a logical model and a physical model (or, minimum, a physical model). I've built databases from scratch this way for decades. It works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2022 at 2:06 pm
Thank you Jeff and Grant for your input!
Yes my conceptual model doc will layout keys, uniqueness, relationships, and attribute rules and constraints. Eventually.
I think one question I had is.... Is it typical to ask a UI or front/back end programming team to provide the database team with these kinds of data “rules”. Ie: asking them to provide if a datum is unique, its max length, default and check rules, relationships/dependencies to other data, etc. Or is this something db team figures out on its own. Clearly, it is much of a joint effort, but just clarifying that it is ok or typical to ask a dev team to provide this type of data info during their dev efforts.
Also, you mention “poor man’s auditing”. I am currently designing all entities with a surrogate key and created_at, updated_at (datetime) as well as created_by, updated_by (variable string or int?). So “rich man’s auditing” would utilize integers. So I would just make sure the updated by columns are integers, using the original login/user_id (which should be an int )
Is there another rich mans auditing method ?
February 9, 2022 at 2:38 pm
I've done database design by sitting with the developers and the business people, both. If you don't have full input, you're guessing. I really hate guessing. I get it wrong, more than 50% of the time, which shouldn't be possible.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2022 at 3:58 pm
You may also want to see if you can get class diagrams from the developers. Even a base class diagram gives you an idea of the required data structures.
February 9, 2022 at 4:38 pm
Thank you Jeff and Grant for your input!
Yes my conceptual model doc will layout keys, uniqueness, relationships, and attribute rules and constraints. Eventually.
I think one question I had is.... Is it typical to ask a UI or front/back end programming team to provide the database team with these kinds of data “rules”. Ie: asking them to provide if a datum is unique, its max length, default and check rules, relationships/dependencies to other data, etc. Or is this something db team figures out on its own. Clearly, it is much of a joint effort, but just clarifying that it is ok or typical to ask a dev team to provide this type of data info during their dev efforts.
Also, you mention “poor man’s auditing”. I am currently designing all entities with a surrogate key and created_at, updated_at (datetime) as well as created_by, updated_by (variable string or int?). So “rich man’s auditing” would utilize integers. So I would just make sure the updated by columns are integers, using the original login/user_id (which should be an int )
Is there another rich mans auditing method ?
Like Grant, I've found much greater success when the "design team" consists of good people on the Web/App Dev Team and good people on the DB Team. In many "previous lives", I always sat in the same area as the front-end and other Developers so I could hear if someone was actually having a DB/Data problem they needed help with and, because I was usually the oldest person in the group, would also protect them from "drive by shootings". It helped knock down a whole lot of common barriers between that DB team and the Front-End team, especially since I started out as a Front-End Developer a long time ago and know the crap they have to put up with.
To summarize, if you don't have the Web/App Dev team involved in the design of the database, they won't learn if they don't know and you're also creating the "not invented here" syndrome between both teams. There's just no need for that type of division. Like I tell people and to quote Red-Green, "I'm pullin' for ya... We're all in this together". "Together" means doing what is best for the company, not doing what is best for inflated egos on either side. And, yeah... it's going to take extra time but, oh my, it's SO worth it.
Shifting gears to the inline auditing, "poor man's auditing" is where the 4 columns you mentioned (I refer to them as the LMB columns named after the most troublesome one, the "Last_Modified_By" column) are the ONLY source of auditing. It doesn't matter if they're INT or not and changing Created_By and Modified_By from VARCHAR() to INT doesn't constitute "rich man's auditing", IMHO. Doing the conversion is definitely the "smarter way" than what people normally do, though.
On the subject of using INTs for Created_By and Modified_By, that's usually going to require at least one "smart" trigger where if a new person or "thing" (app name, stored procedure name, whatever) shows up and it's not in the lookup table that's the source of the INTs, then that trigger will need to add it to the table and then use it during the INSERT or the UPDATE.
On that note, it's a bloody shame that MS hasn't embraced the idea of a true BEFORE trigger instead of those awful "Instead of" triggers.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2022 at 4:44 pm
p.s. As another side bar and just in case it becomes a requirement, remember that anything that the DBAs or others with sufficient privs can affect will NOT stand up in court in-so-far as audits are concerned. I can't tell you precisely what they are (I've yet yet been required to do so) but there are tamper-proof audit methods available in SQL server and by 3rd parties. THOSE methods are what I refer to as "Real" or what you call "Rich Man's" auditing.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2022 at 2:33 pm
Good points about the auditing and class diagrams thanks. Unfortunately some of the leaders are saying the dev and design groups are too busy to work much on providing documentation or to concern themselves with the structure of the db, but i am sure we will have many meetings together so should change.
And Jeff, if you are truly jealous and are itching to build a db from scratch, then please, jump aboard and come help us! Haha. The thing is, it’s a volunteer gig (for now) for a startup project and until another round of funding from the govt comes in, there is no $.
February 11, 2022 at 3:34 am
Unfortunately some of the leaders are saying the dev and design groups are too busy to work much on providing documentation or to concern themselves with the structure of the db...
Heh... Same as always. The leaders want it real bad and they've just signed up for getting it that way. 😉
Knowing that, I'll have to pass on your offer.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply