The way I see it, if you're writing a software specification of some kind, you can either talk in terms of business entities (i.e. a customer order has an expiry date, or the production time must be greater than 0 etc.) or you can talk in terms of code entities (i.e. the data access component will called a stored procedure to do something etc.).

I've worked at a couple of places now, were I seem to get specs full of information about business entities, but which also include a whole bunch of database table definitions. Now, if what I was writing was a solution where the database was going to be talked to directly it might not be so bad (alothough I probably still prefer to implement what they wanted as views and then write the table defs myself), but in this instance the db is completely hidden, the solution is just a bunch of web services. When I try and suggest that the business analysts don't put table defs in the documents, they look at me funny... is it me or what?

Dan
Friday, July 18, 2003

Not you...

Practical Geezer
Friday, July 18, 2003

They belong in the design, unless they are already in existence of course in which case they should be in the requirements. So no, its not you.

Friday, July 18, 2003

Any idea why these people are so keen on defining the database schema. I think maybe they're frustrated former programmers.

Dan
Friday, July 18, 2003

Maybe the spec is being evaluated based on weight.

Joe AA
Friday, July 18, 2003

Where I work, table definitions aren't in the fuctional spec, (which comes from Product Management, and is very high level) but are in the High Level Enginering design, which describes all the product's interfaces.

I imagine our HLDs look a lot like a very well specified funtional spec , except with table definitions added.

We put table definitions there to reduce risk.

As we review then HLD, we can make sure all the functions can be expressed with the table definitions. We believe this gives us a better estimate of how much the function will cost to implement, and based on these estimates, some of the fetaures don't make it into the product.

Steve S
Friday, July 18, 2003

As the data are a result of a lot of dicussions and
features it's not a bad approach for communication.
Most people can understand tables, even if you
have to do a lot archeology on figuring out how
they are to be used.

You don't have to follow it as is however.

The other reason is people use it to control
the project. What i see is the data model
becomes the basis for everything else and
its hard to escape.

valraven
Friday, July 18, 2003

Well, you gotta put'em somewhere.

It all depends on who the audience is. If the spec will be read by people who understand table definitions, then there is no reason to break them out into a separate document. If there is a customer who needs to read and approve the spec, it may be wise to leave out the technicalities.

Big B
Friday, July 18, 2003

We have the same problem here.

I think the problem is that the design team have the idea that they should specify everything so that all we developers have to do is code it.

The table definitions are always awful, and not one member of the team understands the concepts of normalisation.

I've tried to argue that they should keep there specification at the level of intent, and leave the database design to those who have spent a few years studying database theory but they just don't get it.

Seems to me to be another symptom of the 'Programmers as Brickies' mentality.

Ged Byrne
Friday, July 18, 2003

ORM (Object Role Modeling) has been mentioned before several times on JoS, and I think it deals directly with the problem you're talking about, the disconnect between articulating business rules and establishing a logical structure for the database. ORM is concerned with a standardized method of creating a normalized logical database structure from the mere articulation of the business rules. Here's what Halpin has to say about the distinction between business rules and database structure:

Here's a quote from Terry Halpin, the main guy behind ORM:

"A conceptual model is expressed in terms of concepts and language familiar to the business person. It breaks information down into elementary facts, and makes no commitment about how these facts are grouped into structures. A logical model commits itself to grouping facts in one specific way into those specific structures provided by the chosen underlying data model (e.g., hierarchic, network, relational, object-relational, nested-relational)."

"For example, conceptually we may have facts of type: Employee manages Department; Employee drives Car. Suppose each employee manages at most one department, each department has at most one manager, and each employee may drive many cars (and vice versa). In a hierarchic or network model, some of these facts might be
stored as an inter-record link or inside a record structure. In the relational model, the ‘drives’ fact would be stored in a Drives table, but the ‘manages’ fact could be stored in an
Employee table, a Department table, or a separate Management table. In an objectrelational model, we might store both facts inside an Employee table by using a set type for cars. All of these choices about how to store the conceptual facts are implementation decisions (at the logical level), and have nothing to do with how a human being conceives of the business."

MS is making a big push behind ORM. There is a freely available ORM design tool (MS VisioModeler) and ORM is built into Visio Enterprise Architect, I believe. Anyone interested can find out more at www.orm.net or www.ormcentral.com .

Herbert Sitz
Friday, July 18, 2003

Dan: This is a common misconception among developers using relatively small schemas. Just because you are a good programmer and you know ANSI SQL does not mean you can replace a DBA. One does not have time to do both things perfectly. When your DB schema will be 10 years old and will have thousands of tables on servers spread across a continent or two you will think differently.

On big systems I prefer to work with a DBA to create and update the DB schema. There is more to Oracle and MSSQL than meets the eye.

Also, most of the time the tables have more uses than just one application. One thing that comes to mind right away is generating reports (maybe using Crystal Reports). CR is very picky when it comes to generating and customizing the queries. CR puts most of the restrictions on my current DB development.

19th floor
Friday, July 18, 2003

If the people writing the functional spec, the table design should give you some valuable information as to what they want to be able to store and query. I would take it as a suggestion and include the table definitions that you really are going to use in your design documentation.

pdq
Friday, July 18, 2003

19th Floor,

I think Dan is talking about when Business Analysts draw up the schema's, not DBAs.

Ged Byrne
Friday, July 18, 2003

Ged Byrne: Nevertheless, for complex schemas the developer is as qualified to do changes as a business analyst.

19th floor
Friday, July 18, 2003

pdq,

This isn't always the case.

For example, I once had a spec that had a table design with a repeated group of fields. Something like this:

Name1, Address1, Name2, Address2, Name3, Address3

and then some very detailed notes on what to do when a 4th name or address was received.

The analyst must of spent ages working around this restriction of 3 addresses.

It took me a couple of hours to untangle what it was all about.

If they had just said that for each proposal there are between 1 and many individuals, each of which has a name and address I could have implemented in 5 minutes.

Ged Byrne
Friday, July 18, 2003

On the second thought the business analyst you are speaking about is being really helpful. Most of the time I get one line specs like this: “We need to implement an audit for all operations on these n*hundred tables. And by the way please make sure it is available for 7 years.”

19th floor
Friday, July 18, 2003

19th Floor,

That sounds like a good spec to me.

Take that 7 year requirement. We would receive a detailed schema and despite constant clarifying and asking the 7 year requirement wouldn't be revealed until, over half a decade later, a user says 'wheres the audit from 6 years ago?'

Ged Byrne
Friday, July 18, 2003

think of them as the requirements for data they want captured, as well as how it relates, I don't see anything wrong with it, plus it makes em feel technical

Daniel Shchyokin
Friday, July 18, 2003

Ged Byrne: Anyways, I did the audit thing, I did even a reporting tool even though they did not ask for it but I knew they would come back and bug me and I left the storage to be dealt by the DBA team. A couple of weeks later the business analysts came running like hell because the table space was full already. It never occurred to them that the audit data ought to outgrow the audited table eventually.