Model validation

[GoogleCodeExporter]

(new feature)

I know it is not the purpose of WWW SQL Designer, tough it would be cool to 
have a quick validation done over the schema.

Simple rules like :
 - [warn] table as no primary key
 - [warn] foreign key index is missing
 - [info] primary key is not numeric
 - [info] table name contains other chars than ^[0-9A-Za-z_]*$

And maybe more rules...

Do you want some code towards this ?

Original issue reported on code.google.com by geompse@gmail.com on 18 Mar 2010 at 4:18

[GoogleCodeExporter]

 - [warn] foreign key constraint does not match with a primary key

Original comment by geompse@gmail.com on 18 Mar 2010 at 4:49

[GoogleCodeExporter]

Do you think this feature should be 

1) implemented on client side,

2) target-db specific?

Original comment by ondrej.zara on 19 Mar 2010 at 6:57

[GoogleCodeExporter]

I think of a client side implementation, these are common warnings that are 
relevant 
no matter what DB kind you use.

Maybe it should be implemented twice, giving db-specific validation, but I'm 
not fond 
of this.

---

Pour la petite histoire, I had to work with an Oracle DBA. He checked our 
team's 
model (a model designed to be db-agnostic because we needed portability), and 
say :
 - names are case sensitive, BAD
 - dates and times are stored as integer (numeric), BAD
 - some data are stored in JSON, BAD

These are typical db-specific warnings, but to us it makes no sense at all, 
since :
 - names are by default lower-case in postgresql and had to be lower-case too in 
Oracle, where names are upper-case by default.
 - dates and times column formats are really different from one SGDB to another
 - the JSON data are computed afterwards by our program, since there is no sql-search 
on it, it saves times by not creating more tables, setting more relations, 
complexifying queries...

The point is that "good practices" must always be rethink afterwards. Sometimes 
they 
are no good for your project.

Original comment by geompse@gmail.com on 19 Mar 2010 at 10:57

[GoogleCodeExporter]

Okay, makes sense. Some small set of client-side DB-agnostic checks sounds like 
a
reasonable idea.

Original comment by ondrej.zara on 19 Mar 2010 at 11:14

• Changed state: Accepted
• Added labels: Type-Enhancement
• Removed labels: Type-Defect