Hi,

Last night I was working on a relatively new project. This got me thinking... How do others tackle such a task?

So here is my question:

  When faced with a new database related project, where do you start? Do

you first design the database schema, then build the BOM (Business Objects) accordingly? Or, do you first design the BOM, then design the database schema to fit.

Personally, most of my project were started by first creating the database schema. Don't ask me why, that is just what I did. Maybe it was because many of the tools I use, can write much of the boiler plate code for me, and they do that by querying a database structure. But I've been thinking that with my next project, I'm going to start with the BOM first, and see how it goes. I'm assuming the end result should be the same, no matter which part I tackled first.

What's your thoughts?

Regards,
- Graeme -

2015-06-26 13:30 GMT+02:00 Graeme Geldenhuys
:

Hi,

Hello,

IMO, it depends on the complexity of the project. In simple projects (a few tables), usually the BOM maps exactly to the database, so I do both at the same time. But in complex projects, BOM can be significantly different from the database structure. For example, the BOM could include things which would actually map to aggregated views. Usually I first design the BOM according to the requirements, then I rework the database structure, including for example things which were not requested but which I feel will be useful later (timestamps, history tables, intermediate calculation steps, future developments...).

One classical example: if I am asked to create a table with name and address, I actually create a table with the name and a separate table with the addresses, so that when (this always happens) the client discovers that people have more than one address, the database structure is ready, but also the code using that structure is ready too, or at least the modifications to that code are going to be simpler. In my example, the BOM still has the address included in the same object as the name, and the UI can behave as if this was physically true.

In a way, separating BOM from physical structure brings the same kind of advantages that structured programming does. Just as I can look at a piece of code from a higher level or right at the deepest code in the deepest routine, depending on my needs, I can look at the data from a wide point (usually using views or complex queries) or break the data in smaller parts and look at the physical data directly in the tables.

Il 26.06.2015 13:30, Graeme Geldenhuys ha scritto:

Hi Graeme

I normally start designing the database, then comes the BOM, and then I start developing the UI with its functions and procedures; during this process, the database normally gets slightly revised and completed with stored procedures, views and triggers. Last, but not least, I develop the reports (that is the part I love least).

But I think this is a matter of personal choice.

Best regards
vince

As it happens I am doing pretty well the same thing right now - building a new application to help me with a requirement capture assignment I am starting next week.

This type of application is data driven and that, to me says it all. You start by defining the data model eventually ending up with the SQL DDL for your database and that in turn drives the application design, with typically a form/frame for each of the primary tables.

Of course, pure top down design only ever happens in books or programming courses and there will be an element of iteration, going back to the data model as the application design progresses, but the fundamental is data model first and application second.

Also, I'm working with Firebird/IBX and IBX gives your a lot of design time help to build and test the SQL queries. This only works if you have a test database available, further emphasising the need to get most of the data model in place before building the application.

Tony Whyman
MWA

IMHO, doesn't matter what came first because they are different things. If you are coding using OOP, you do not need to following the structure of database and vice-versa.
Objects should map behavior; databases map only data. So, they are completely different things.

Regards,
Marcos Douglas

I always start with the data. Almost all applications I ever wrote have databases. The data structure is in my experience always clear, simple. And thus I consider it a good foundation for the rest.

Things start to get messy when you start thinking about functionality. So that comes next, and the business classes reflect functionality, and consequently more susceptible to change.

Luckily the persistence layers I use are flexible enough to allow any mapping between data and business objects, so the above approach has always worked for me.

I'm tempted to say that working the other way round is bound to lead to confusion.

Michael.

Confusion... That depends if you work using a data-driven design or behavior-driven design.

Marcos Douglas

Graeme Geldenhuys wrote

When faced with a new database related project, where do you start? Do you first design the database schema, then build the BOM (Business Objects) accordingly? Or, do you first design the BOM, then design the database schema to fit.

I'd suggest that the answer to that varies, in part depending on the type of program.

In the case of a business (or logging etc.) program where a database might have multiple "client" programs, possibly written by different people in different languages, then the principle of top-down design suggests that you start with the schema.

In the case of a program which is using a database for private backend storage and as a configuration registry, then top-down design suggests that schema design might be an ad-hoc activity at a low priority with (at least) a UI mockup done first.

However a common feature is that in both cases a developer would do well to make an early decision as to whether e.g. new rows replace existing ones or are appended, whether e.g. modifications are timestamped and branded with a user ID, and so on. Which suggests that there are some invariant activities which can usefully be done as soon as it's decided to use a database, and possibly before the database implementation is even selected.

Hi Graeme,

As mentioned in one of the earlier replies, it really depends on the complexity of the application and the state of the Business Rules.

If I have a detailed definition of the Business Rules (a really rare occurrence), I will start with the BOM and UI. I sometimes build in some data fixtures without ever touching the database till I am reasonably sure that the BOM/UI and Fixtures describe the Business Rules. Then I will start moving the fixtures into the schema one or two at-a-time with constant testing.

When I don't have a detailed definition of the rules (the usual case), I start with top-level rules and one or two top-level tables, their associated BOMs and UIs. After each set of BOM/UI/Tables I sit down with the customer and demonstrate what I have so far. We then agree on any changes that must be made and we iterate till that set of requirements are being satisfied. Often we also agree on what part of the rules should be implemented next.

Most of the time, the customer "waves her hands" to describe what the program must do, so it is up to me to actually start developing the Business Rules. When that happens, the second approach helps by reducing the amount of throw-away code required till the customer gives his/her approval. (Especially when the customers are my own daughter and grand-daughter).

My most recent project was a translation of a pre-Windows c++ program with a CLI for the user interface and no database into Lazarus with sqlite3. Since the rules were well established, just implemented with very old technology, I used the first approach and it worked very well. I had almost the entire program working in test mode before I had to touch the database for production.

Hope this helps.

Don Ziesig, ROP (Really OLD Programmer);-)

Always data.
Data exists on itself. Behaviour never exists on itself. The moon is there, whether you look at it or not.

Michael.

Graeme Geldenhuys wrote

What's your thoughts?

That's very interesting... so it is unanimous that starting with the database schema is the way to go. So all these years of doing that was at least the norm. :)

Thanks for everybody's feedback on this.

Regards,
- Graeme -

+1

Marcos Douglas

So, you use the data-driven design and nothing is wrong with it... but behavior-driven design is another approach to resolve problems too.

Marcos Douglas

Sure.
I was just commenting on why I personally think data-driven design is more "correct" than behaviour.

Michael.

Maybe we should store compiled unit interfaces in a database instead of using .ppu ;)

We already do: a unit is a database, using a proprietary format ;-)

Michael.

If you want to do ORM per se, define how objects are mapped, and then BOM and schema are fairly equivalent, and are designed in step.

But in production circumstances make sure you always can override the mapping so that you can also use existing tables with some custom code.

If you have too many preexisting tables, forget about ORM.

Often there isn't even a choice, and there is already a database layout partially existing.