The ORM Foundation

How can I define column order for columns in my tables in ddl output.

 Thank yuo for help

Hi Oliver,

Have a look at this thread.  http://www.ormfoundation.org/forums/p/565/1674.aspx#1674

You can also try a search on "column order".

Let us know if this does not answer your question.

Ken

Hi Ken,

 Thank you for your help, but  I do not find solutions for my problem yet. 

In firs image you see ORM model.

In second image you see ER model

In third image you see DDL that generate Norma.

In table Patinet I want column isSmoker to be before column patientName. Is it possible? You also see that column order in ER and DDL is not the same.

Hi Oliver,

Thanks for the diagrams. It always helps to use examples.

The relational model (and set theory) do not require columns to be in any order.
The implication of this is that an SQL query will work regardless of the order of the columns.

Since you probably know this, it would be helpful if you can explain why you want the columns in the data model to be in a particular order.
How does ordering the columns in the data model help you to achieve your objective?

 Thanks

Ken

Hi Ken,

I agree that column order does not important from that point of view but I think that is good practice order columns in data table.

oliver:

but I think that is good practice order columns in data table.

Oliver, would you mind saying why you feel it is good practice? Does your feeling change when you realize the DDL was generated by a tool, and that the order of the columns does not equate to the intent of a human designer?

Having asked that, I have my own reasons for wanting to be able to set the order. After reverse-engineering an existing database, I've needed to enhance the conceptual model, then generate DDL for the changed model. I need to be able to compare the schemas between the old and new. If the columns generated by NORMA are in random order, change varchar to nvarchar and int to decimal, then I'm going to have a very hard time creating useful change scripts, or even knowing what actually changed.

John Saunders:

I need to be able to compare the schemas between the old and new

Hi John,
On the surface, comparing the reverse engineering results with the "original" sounds like it might be a good reason for being able to order columns.

The issue I see here is that many potential "source" databases are not likley to be in 5NF and they will probably contain many anomalies. Thus, after reverse engineering to ORM and then validating the facts with domain experts, you will probably generate a logical schema that bears little resemblance to the original "source" schema. In such a situation, column order would be only one of many factors to "compare".

Having said that, I would also like to know from Oliver the basis of his "good practice" comment.
Oliver, I am wondering what it is that you want to do with ordered columns that you can't do with unordered columns?

Ken

Ken, having just gone through this painful exercise, I'll ask you to consider the case where only 10 or 20% of the conceptual schema changes after reverse engineering in ways that will affect the mapping to DDL. In particular, this is a data warehouse, and most of the dimension tables had no need to change at all.

In many cases, the only changes were trivial, but needed to be seen to be trivial as well. For instance, I made several "name" objects mandatory, and a few were made one-to-one unique. In this case, a schema compare should have shown only a change from NULL to NOT NULL, and should have shown the addition of the UNIQUE constraint.

Seeing a change in column order was distracting, to say the least.

Hi,

In firs image you see ORM model.

In second image you see DDL that generate Norma.

In third image you see "good practice". There is no things that I can do with ordered columns that I can't with unordered columns but....

 I agree that it is highly desirable when viewing relational tables to have them ordered in a way for convenient viewing, e.g. list the most important columns first (e.g. PK columns) and keep related columns grouped together (e.g. parts of an address or name).

Unfortunately, NORMA does not let you do this yet (other than your manually reordering the DDL code it that it generates). Control of column order is one of our high priority items to add to NORMA in the future, and is included in our specification of a much better relational designer for a future release.

Cheers

Terry

Cheers to you to Terry and Ken and John 

Thanks, Terry, I'll look forward to seeing these abilities grow over time.