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

  • 6
    @gnat: That is completely different. – Robert Harvey Dec 2 '14 at 22:04
  • 1
    If this gets closed as a duplicate, it should be a duplicate of this question. The answers and question are more in line with what I'm asking. – RubberDuck Dec 2 '14 at 22:34
  • 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 3 '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 Dec 4 '14 at 20:48
up vote 82 down vote accepted
+50

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.

  • 1
    "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 3 '14 at 1: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 3 '14 at 15:50
  • 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 5 '14 at 16:14
  • 12
    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 6 '14 at 17:37
  • 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

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.

  • I agree whole heartedly, but that's not going to happen on this one. Thank you for your time though. – RubberDuck Dec 2 '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 3 '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 1: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

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.

  • 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 3 '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 3 '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 3 '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 3 '14 at 19:06

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.

  • 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 3 '14 at 1:34
  • 1
    @RubberDuck I added a clarification at the end of my answer. – Tulains Córdova Dec 3 '14 at 10:04

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.

  • 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 3 '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 Dec 3 '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 3 '14 at 17:16
  • If you don't know where you are going, any code will get you there. – user251748 Jun 2 '17 at 13:40

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.

  • 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

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 :)

  • 3
    this doesn't seem to offer anything substantial over points made and explained in prior 5 answers – gnat Dec 10 '14 at 12:55
  • My point is to follow approach depending on type of client – Kein IhpleD Dec 10 '14 at 12:57

protected by gnat Dec 11 '14 at 15:38

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.