Create table Dynamically(at run time)

Question

Hi,

I am working on fleet management. I want to create one table per vehicle ie when I create a vehicle I want a table to be created for that vehicle at run time. Now I am writing a procedure which creates table but table with same name is not created. How to achieve this?

Any alternative?

Technology: ASP.NET/MYSQL

What If I go for portioning??????

Answer 1

this sounds like a very bad idea, but if you insist you can simply send a CREATE TABLE query just like any other query.

create your table once using PHPMyAdmin or a similar tool to see the exact CREATE TABLE command, and use that as the template to create similar tables.

another option is the CREATE TABLE LIKE command, which will create table with the same schema, but without data.

Answer 2

The alternative is to do it the right way - with 1 table that stores all vehicles.

Your boss's solution will make it next-to-impossible to query the database. Explain this to him, and if he still insists that you do it "his way", tell him he's a moron.

Answer 3

You can get the best of both by partitioning the data, but leaving it in one table. See here for an example, and here too

Answer 4

Doing that would be a terrible misuse of a relational database.

Has he got a good reason why you should use that design?

Are there any good reasons why anyone would ever want to do things this way? (I am genuinely interested if there is a valid use case)

Answer 5

It is indeed, as other people have already said, a VERY bad idea to have one table per vehicle. A much sounder approach would be to create separate tables for separate kinds of vehicles, in case they need to store different data. It could look something like:

vehicles 
    {vehicle_id, type, name, commission_date, [other data]}
freight_vehicles 
    {vehicle_id, load_capacity, [other data]}
passenger_vehicles 
    {vehicle_id, passenger_capacity, [other data]}

assignments 
    {vehicle_id, from_destination, to_destination, 
     started_at, finished_at, [other data]}

destinations 
    {destination_id, name, [other data]}

Since you probably know more about database design than your boss, take the time to prove your case by:

1. Explain to him the implications of his design. I.e. hard to query, inefficient
2. Explain to him the benefits of a proper design. Easier to query, follows conventions etc.
3. Explain to him that he won't lose the ability to export data about single vehicles, but rather that exporting (and maintaining) the data will be much easier with a more sound design
4. Ask him to explain his reasons why he wants the proposed design in the first place, and take any of his concerns seriously. Explain to him how your design can solve those concerns, and how much more difficult it would be to solve them with his proposed design.