How can I automatically determining table(s) schema from a set of queries?

Question

Is there any tool, which will take a set of CRUD queries, and generate a 'good enough' table schema for that set:

e.g. I can provide input like this:

  insert username, password  
  insert username, realname
  select password where username=?
  update password where username=?
  update realname where username=?

With this input, tool should be able to make either 1 or 2 or 3 table, take care of _id's, and indexing.

To put it alternatively, i'm looking for a tool, with which, i can design set of queries assuming a single infinite column table, and tool process and actually generates a number of database/tables/columns, and a high level language module with function calls to each of query.

oh yes , i'm trying to fire my db designer (-:

Answer 1

Have you considered using a ORM solution like Hibernate? This requires a inital set of mappings between the application class model (for example the User class) and the database schema representation (eg: USER table).

An ORM solution may supports advanced mapping scenarios where an object maps to more than one table in the schema. Also newer versions of Hibernate supports generating the database schema from the mappings (search for hbm2ddl tool).

Answer 2

You're asking for the impossible.

• How would the tool know that username should have an index on it, much less a unique index?
• How would it know the data types of the columns?
• How would it know any domain constraints — for example, a hypothetical sex column must be either male or female, not crimson?
• Wouldn't it be pretty vulnerable to typos, leaving you with a username and a user_name column?

Databases require design for a (well, many) reasons. Questions of normalization, for example, are going to be very difficult for a tool—which can't understand your problem domain—to answer.

That said, it isn't automatic, but what your asking for is—as Aleris answered—an ORM. You didn't specify which language you are using, but surely there is one (or more) for yours.