value by default 0

ok, I understand better your point of view and I think we are not far for
aggree.

The only difference is about foreign constraint :

I aggree that they can be set in the database as they can be easily ported
(not like trigger and store procedure) to different databases.
With my assumption (data access is allowed only with ORM), the foreign
constraints in the database are just redundant and not useful.
But I have another practical reason :
I tryed a long time ago to set the foreign constraints as well in the
database. But in some circonstance when deleting part of the graph, the
order of the delete generated by my ORM are impredictible and this cause
problem.
Not a good reason and may be already solved but I never trie again.

Pierre


"Martijn Tonies" <m.tonies@upscene.removethis.com> a écrit dans le message
de news: 476a712e$0$4207$e4fe514c@dreader24.news.xs4all.nl. ..
> Hello Pierre,
>
>> I just can agree on one think :
>> The model of an application must be in only one place but not in a mix of
>> both. That was the origin of my problem.
>> Your model is build from tables of a database and that's ok.
>
> This is not my view at all. My programming model has nothing to do
> with the fact that I always put constraints in place on the database
> level.
> I've designed systems with an object approach and designed tables
> afterwards BUT the tables are normalized so that no data gets duplicated
> or becomes invalid due to programming errors. This includes constraints
> on the database level separated from your object model.
>
>> My model is a graph of objects and then all fonctionnalities and
> constaints
>> must be implemented at that level.
>
> Of course, this is perfectly valid and the same as with my projects. Your
> constraints and functionality is available in your programming language,
> object oriented, PHP scripting, whatever.
>
>> This allows me to change the persistence (from Oracle to MySql or even a
>> flat files or XML file) without effort.
>
> This has nothing to do with database constraints!
>
>> I change my database from Oracle to mysql and I get this problem because
>> I
>> forget (or loose it) to set a constraint in the object model.
>
> See above, your constraints need to be in place in your object model, this
> is correct and makes sense.
>
>> Then the database was in charge and they have different behavior !
>> In theory a persistence is not even required but in the real world, I
>> have
>> to recreate the last graph after a crash or maintenance.
>> In this model, there is no concept of primary and foreign key as they are
>> part of the actual persistence system used. It's the job of the ORM
>> (Object-relationnal mapping) to take care of the underlying persistence
>> system.
>> Of course, I am aware that nothing else than the ORM must access directly
>> the database/files.
>
> Which may-be beyond your control AND of course, the point about the
> failed constraints or bugs still stand.
>
>> It's not that you intend to suggest but you help me to have clearer view
> on
>> this matter.
>
> I agree with your constraints approach in your object model, this is
> of course perfectly normal and required for your model to work
> properly.
>
> However, the data is the most imporant part of any application or set of
> application that actually needs to store data. This is why you should put
> constraints on the database level as well. It avoids invalid data despite
> programming errors, external/3rd party applications accessing your data,
> other programmers working on your object model etc...
>
>
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle
> &
> MS SQL Server
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
>

> ok, I understand better your point of view and I think we are not far for
> aggree.
>
> The only difference is about foreign constraint :
>
> I aggree that they can be set in the database as they can be easily ported
> (not like trigger and store procedure) to different databases.
> With my assumption (data access is allowed only with ORM), the foreign
> constraints in the database are just redundant and not useful.

Redundant? Your point below shows exactly why they are not redundant.

> But I have another practical reason :
> I tryed a long time ago to set the foreign constraints as well in the
> database. But in some circonstance when deleting part of the graph, the
> order of the delete generated by my ORM are impredictible and this cause
> problem.

Apparently, you trust your ORM to delete everything, but it has bugs. This
shows the imporance of constraints, as it might not save everything or
delete
everything or make errors here and there.

Exactly why you should protect your database and your data.


Hey, it's all up to you, but if someone writes me a database application and
he says to value the data, but does not use constraints, I'm not going to
take
that application.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

"Peter H. Coffin" <hellsop@ninehells.com> wrote in
news:slrnfml144.b1k.hellsop@abyss.ninehells.com:

> On Thu, 20 Dec 2007 11:48:10 +0100, Martijn Tonies wrote:
>> This is nothing but common sense. Read Peter H Coffins post, he
>> explains it in more detail.
>
> I do try...
>
> Some day, there'll be a mechanism to grant authorities to stored
> procedures instead of users, and life will really get fun. The long
> goal of being able to actually enforce things like "only User_X can
> delete rows with 'User_X' in column user_id" by granting DELETE to the
> procedure and taking it away from all users, forcing deletes through the
> SP, will actually be achievable. We can kinda do it with triggers but
> it's very messy.
>

Oracle has Fine Grain Access Control which provides this capability.