Code First vs. Database First

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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