81

When I design and create the software I work on, I typically design and create the back-end SQL tables first and then move on to the actual programming. The project I'm currently working on has me down right puzzled though. This is probably due to a lack of good, solid requirements, but there's unfortunately little I can do about that this time. It's a "just go make it happen" kind of situation.. but I digress.

I'm thinking of flipping my workflow on it's head and creating the UI and data model classes first in hopes that working that out will make it clear to me what my database schema will eventually look like. Is this a good idea? I'm nervous that I'll end up with a UI and still no idea of how to structure the db.

If anyone is curious, I'm using SQL Server as a backend and MS Access as a front end application. (Access isn't my choice either... so please don't hate on it too bad.)

RubberDuck
  • 8,911
  • 5
  • 35
  • 44
  • possible duplicate of [Front end first or Back end first. Of the two which is a Good system design pratice?](http://programmers.stackexchange.com/questions/55883/front-end-first-or-back-end-first-of-the-two-which-is-a-good-system-design-prat) – gnat Dec 02 '14 at 22:01
  • 6
    @gnat: That is completely different. – Robert Harvey Dec 02 '14 at 22:04
  • 1
    If this gets closed as a duplicate, it should be a duplicate of [this question](http://programmers.stackexchange.com/q/52681/142319). The answers and question are more in line with what I'm asking. – RubberDuck Dec 02 '14 at 22:34
  • Is this person code, or work? What I really want to ask is if you can refuse to begin (and waste your time re-doing everything later) until the requirements are (close to) nailed down. At which point, obviously, design your db first as part of higher level design (as, it seems, you are wont to do) – Mawg says reinstate Monica Dec 03 '14 at 16:21
  • 1
    @Mawg it is a work project. I have pushed back as much as I can about getting the requirements nailed down. Work has to begin on this and there's nothing more I can do about it. – RubberDuck Dec 03 '14 at 16:24
  • 4
    Errrm, new job? I know that I would. But as a 30 year freelancer I find it easy to walk away before it really hits the fan (some people you just can"t help), but I realize that it is not so easy for all. Stay if you must (no comparable employer in the area, etc), but don't stay if it starts to affect you. – Mawg says reinstate Monica Dec 04 '14 at 20:48
  • Some ideas; 1. Make a wireframe or alike to get the requirements and specs (more) clear. 2. Create models 3. perhaps use a different database backend which is more suited for agile work, maybe even NoSQL like MongoDB. – Rudger Dec 11 '14 at 11:37
  • Since it depends on situation mostly this is what I am faced; think about your are consuming Facebook API, what you would to? According to their data structure you create decorator around it. So you shape your code according to data source but decorate it. Similar way you create your Database then create/generate DAL (Db First) and write your ClassLibrary (decorator) around it. If you schema will change only your DAL will be effected and schema changes (according to me) should start from DB and would not effect Business Layer. DAL will be POCOs generated through database. – Teoman shipahi Jul 28 '15 at 18:16

7 Answers7

86

What came first, the process, or the data used by that process? I know this is kind of a "chicken or the egg" question, but in the case of software, I believe it is the process.

For instance, you can build up your data model incrementally by implementing a single use-case at a time with just in-memory persistence (or anything as easy to implement). When you feel you've implemented enough use-cases to outline the basic entities, you may replace the in-memory persistence by a real database, and then continue to refine the schema as you go forward, one use-case at a time.

This takes the focus out of the database and moves it to the core of the problem: the business rules. If you start by implementing the business rules, you'll eventually find (by a process very similar to Natural Selection, by the way) which data is truly needed by the business. If you start by modeling the database, without the feedback of whether that data is truly needed (or in that format, or in that level of normalization, etc...), you'll either end up doing a lot of late adjustments in the schema (which may require heavy migration procedures, if the business is already running with it), or you'll have to implement "work-arounds" in the business rules to make up for the out-of-tune data model.

TL;DR: The database depends on the the business - it is defined by them. You won't need the data unless you have a process that operates with it (a report is also a process). Implement the process first, and you'll find which data it needs. Model the data first, and you may just be able to count how many assumptions were wrong when you first modeled it.

A little out of the topic but very important: the workflow I describe is often used along with very important practices such as "The simplest thing that could possibly work", test-driven development, and a focus on decoupling your architecture from the details that get in your way (hint: database). About the last one, this talk sums up the idea pretty well.

MichelHenrich
  • 6,225
  • 1
  • 27
  • 29
  • Often times the data is in spreadsheets, paper and legacy databases. Often times the process already exists but done manually o with several disparate tools. – Tulains Córdova Dec 03 '14 at 01:17
  • 2
    "The database is a detail". Thank you very much for the link. I whole heartedly believe that I will be able to tackle this project leaving the database as a deferred decision after watching that talk. – RubberDuck Dec 03 '14 at 01:37
  • 6
    And just to put a name on it: These practices are all (arguably *the*) important practices in Agile development (incremental development, the simplest thing that could work, test-driven, user needs first...). – sleske Dec 03 '14 at 15:50
  • I have seen both. Sometimes the process comes first, sometimes managing the data comes first. But in all cases, problem analysis and system design come before building tables and coding objects. – Walter Mitty Dec 03 '14 at 16:54
  • While I generally agree with this answer, I have known a lot of "database guys" who would vehemently disagree. – user949300 Dec 03 '14 at 17:18
  • @user949300 of course they'd disagree, they are "database guys"! You gotta make a living, right? :) But seriously now, being someone who started out as a DBA and moved into all-round software development, I can tell you that there are some pretty bad "best practices" out there. Just because a lot of people do something, it doesn't mean they're right. Take Waterfall, for instance. Anyway, my point with this answer is simply to show how making decisions without being fully informed is a risk, which is exactly what most people do when they design the database first. – MichelHenrich Dec 03 '14 at 19:37
  • @WalterMitty The keyword here is feedback. Activities that bring you feedback about what you're building should come first, because they inform you instead of forcing you to make assumptions (each being a risk of its own). It's quite like comparing financial assets and liabilities. For software, designing a database (or anything at all) without a "proven" need for everything that is being put there, is likely going to be a liability. :) – MichelHenrich Dec 03 '14 at 20:02
  • 8
    I wanted to come back and thank you again. I have my entire middle tier working *without a database* and I now have a great grasp on how the data should be persisted. I can't thank you enough. I'd upvote again if I could. – RubberDuck Dec 05 '14 at 16:14
  • 14
    If you truly capture all the requirements through your code-first method, *and you truly express all those requirements in your final database*, I can agree with this answer. But I have seen many, many projects where the satisfaction of getting something "working" results in the attitude that "if it's working, the database must be good enough", even when it is a **HORRIBLE** database design, with inevitable and serious performance problems later. Also, a great many people seem to think if the code is validating data you don't need CHECK or FOREIGN KEY constraints. **You do**. – Ross Presser Dec 06 '14 at 17:37
  • 1
    @RossPresser agreed. As with any practice, there must be discipline. I haven't focused my answer on the decoupling process that should also occur with this "code-first" approach, but the talk that I linked to presents the idea in detailed way. In the end, if you have your database decoupled, there should be no excuse for optimizing the DB, because it becomes trivial to even replace the DB completely (in terms of code, not data migrations, of course :P). – MichelHenrich Dec 06 '14 at 18:55
  • 2
    Possible this is covered in the video -- unfortunately I can't get to that right now -- but another advantage of the incremental approach is that when done correctly it helps solidify vague specifications. "Does this screen look like it's capturing all the contact information you need? Are the fields arranged in an order that makes sense with your workflow?" Being able to ask these questions -- with something concrete as a reference -- is IME frequently the *only* way to get the information you need. – David Dec 10 '14 at 13:37
19

A root cause analysis suggests this problem is not one of method, but is the lack of a specification. Without one it doesn't really matter what you write first - you are going to throw it away anyway.

Do yourself a favour and do some basic systems analysis - identify some users at various levels, make up a quick & dirty questionnaire then turn off your machine, grab some coffee and cookies/donuts (or whatever greases the wheels) then take a walk to their desks, ask them what they do and what they need to know/record to do their job even if it seems obvious - still ask. Don't worry about how important they are, if they're too busy then make an arrangement to come back another time or leave it with them.

Once you have that you should be able to start building whatever you think will give you the best results and wait for the rest of the specification to come in.

James Snell
  • 3,168
  • 14
  • 17
  • I agree whole heartedly, but that's not going to happen on this one. Thank you for your time though. – RubberDuck Dec 02 '14 at 22:35
  • 9
    If you don't have time to do it right, where will you find time to do it over? – Walter Mitty Dec 03 '14 at 16:54
  • Who said anything about not doing it right @WalterMitty? Please see the video link in the accepted answer. The database is a *detail* that doesn't need to be in place to work the kinks out of 95% of the software. – RubberDuck Dec 10 '14 at 01:24
  • 1
    I took "that's not going to happen" to mean you are going to proceed to code without even a clue as to what information the stakeholders need from the system. I think James gave you very good advice on doing basic systems analysis without becoming mired in analysis paralysis. – Walter Mitty Dec 10 '14 at 13:03
  • You misunderstood me @WalterMitty. I've taken a feedback loop approach. I'll build what I think it should (without a database) and then take it to the users. Modify the program. Repeat. After a few iterations of that, I should know exactly what the database will look like. As I understand it, the Agile approach is specifically designed to cope with unclear requirements. It's see in me well on this project. – RubberDuck Dec 10 '14 at 22:27
  • It sounds like the feedback loop approach is your way of finding the time to do it over and over. As long as you limit your time investment in each iteration, and as long as it leads you to a correct spec for what the users really need or want, then it may work out well for oyu. Good luck! (no sarcasm) – Walter Mitty Dec 11 '14 at 12:43
15

My experience is as follows:

  • In most projects I've worked on, we designed the database first.
  • Often times data already exists in spreadsheets, legacy databases, paper, etc. That data will give you hints about the tables you need to hold it.
  • Often times a process is already being used, yet manually or using several, disparate tools that are not automated, don't inter-operate and/or don't conform to standards.
  • Once you have a semi-mature database model you can begin designing prototype forms, windows etc., that read and write to that database.
  • As you continue, some changes will be necessary to accommodate for things not contemplated at first.

Also remember:

  • It's no longer a one-app<->one-database world. Maybe your app will have to read or write data from more than one database or your solution will comprise more than one app using the same database.

Conclusion: I recommend you to design the database first.

Tulains Córdova
  • 39,201
  • 12
  • 97
  • 154
  • Those are all very true things, and in fact this *will* be replacing a "non-process" and spreadsheet, but I fail to see how this is an answer to my question. Can you please clarify? – RubberDuck Dec 03 '14 at 01:34
  • 1
    @RubberDuck I added a clarification at the end of my answer. – Tulains Córdova Dec 03 '14 at 10:04
13

I was going to say Database First since I have a lot of experience with large projects and you really need a solid data model if you have many developers working in parallel.

But then I though about it a little more and I realized that what we were really doing on the more successful large projects was "requirements first".

A good well specified set of business requirements, leads on to a good set of functional requirements. If you have a good set of functional requirements then the data model and module specs just fall in to place without much effort.

James Anderson
  • 18,049
  • 1
  • 42
  • 72
  • 1
    This is exactly how I typically work. *Requirements first*, yes. How I wish I could drag some solid requirements out of someone right now. – RubberDuck Dec 03 '14 at 11:09
  • Requirements first, though you should not wait until requirements are *complete* (they never are). Get going once you have enough to have an idea of what the goals of the application are, then get more when you need them. – sleske Dec 03 '14 at 15:52
  • @sleske -- A good analyst should get a feel for the more "dynamic" (i.e. vague and changeable) parts of the requirements and build some flexibility into the design to easily cope with the users whims. – James Anderson Dec 03 '14 at 16:33
  • 1
    @JamesAnderson: Actually, I'm a huge fan of agile development principles, where you usually only design for what you need *now*, unless you know you cannot change the design later (rarely the case). But I'm beginning to go off-topic... – sleske Dec 03 '14 at 19:06
7

Since this seems so fluid/unspecified, I'd do the frontend GUI first - that sounds like what you need to get responses, support, time, and feedback from the stakeholders, right? They don't care about your brilliant normalized tables and foreign keys constraints and cascading deletes. But a cool GUI with lots of shiny colors - well, that's top notch!

For the initial backend "database", use something extremely simple, maybe just keys/values stored to a file. I'm unfamiliar with MS Access, so don't know what the "lightweightiest" backend would be. (a spreadsheed table?) Whatever is quick and dirty, plan on throwing it away.

If you can, use a funny look and feel in the GUI to make it clear that it is a prototype. If all else fails, use index cards.

Now, maybe your stakeholders are DB experts - that has been the case with me sometimes! - in which case, do some DB designs.

user949300
  • 8,679
  • 2
  • 26
  • 35
  • 3
    +1 for neither "code first" nor "database first" but "non functional gui-prototype first" (aka "rapid prototyping"), because in the absence of requirements the gui prototype helps to clarify the requirements with endusers. – k3b Dec 03 '14 at 10:20
  • 1
    True, but it also makes them believe that the app is as good as done. I have been burned that way before & now demand that we get the requirements straight first – Mawg says reinstate Monica Dec 03 '14 at 16:22
  • @Mawg yes, unfortunately that is a danger. One option (in Java at least) is to use a weird "look and feel" to make it clear that this is a prototype. – user949300 Dec 03 '14 at 17:16
  • If you don't know where you are going, any code will get you there. –  Jun 02 '17 at 13:40
0

Since the requirements are not clear one can start with a very rudimentary data model with the key elements you will know you need, maybe just basic tables and PKs to start. The rest of the data, serialize to binary or XML and store the BLOB in the database to start with. That should allow one to develop the UI and business layer (middle tier) without a fully relational model but you will still have persistence to save and retrieve and simple key lookups as needed.

As an example, maybe you have a Person, so you have a PK of Person Id. The rest of the attributes are not known so just start with a Person table with a PK Person Id and another column that will store the Blob, all the person data.

Once the requirements solidify take your Blobs and extract all the tables and columns needed and make the model relational. Then it's just a matter of changing the persistance from a BLOB to relational in the data access layer. But everything else, business rules UI etc will still work. Note, this adds some time to the project but gives allows for complete flexibilty to add and drop things as needed without changing the relational model until things become firmer

Searching may be delayed as you can't query a BLOB so as the model stabilizes, start storing your the data that needs to be searched in relational columns.

Basically you start with a tabular model and move to a relational one as the project progresses.

Or, firm up the requirements prior to any work started so that a relational model can be developed initially.

Jon Raynor
  • 10,905
  • 29
  • 47
  • This way madness lies. Never persist data until you're ready to persist the data. Normalizing data after the fact is a nightmare. – RubberDuck Dec 11 '14 at 21:55
  • 1
    There's a difference between persistence and normalization. The question which only you can answer is do I need to persist only, or persist and normalize. A data model is a model, if there are no requirements, then its tough to model something relationally. – Jon Raynor Dec 11 '14 at 22:01
-1

In general i think code comes after data because the code is going to manipulate the data.

If requirements are not clear you can create a data model of your interpretation of the requirements. Best is maybe to write down some requirements and send it to your employer, then they have something to shoot at. Or create a gui first, it depend of the type of employer what works best :)

Kein IhpleD
  • 17
  • 3