CODEWITHSUNDEEP
javamysqloracle-database
Debobroto Das
Debobroto Das

Reputation: 862

How to decide whether a table in the database exists or not?

I am trying to update a database table and the table's name format is employee_yearName_monthName. because I want to keep each month's data on a separate table.

Now at the start of the month there is no table for that month. SO i should create the table. But the question is how to decide whether the table exists or not?

thanks in advance.

Upvotes: 1

Views: 109

Answers (2)

ppeterka
ppeterka

Reputation: 20736

I want to keep each month's data on a separate table

This is seriously BAD design! If you have huge amounts of data (at least in the range of 50-100GB, around a hundred million rows), rather prefer Partitioning (-->Oracle, -->MySQL. If you don't have that much data, just don't worry, store the data in one table, with the proper columns, and set up proper indexes!

Why is this a bad design? Just think about these:

  • How would you formulate a query for example a yearly report for all users?
  • What if a user has 32 character name? Your table names will be too long for example for Oracle to handle
  • How do you formulate queries about weeks that have one half in one month, the other in the next one?

EDIT as OP disclosed more details Solutions

  • you can refactor the database - that needs management decision. You should evaluate the costs associated with maintaining the current structure vs refactoring the DB, and present this to decision takers...
  • in Oracle, you can use the USER_TABLES management table to query for table names: SELECT * FROM USER_TABLES WHERE TABLE_NAME LIKE 'employeeName% for example.

Upvotes: 2

Maehler
Maehler

Reputation: 6341

As mentioned by others, this is not a good design. However, if you want to stick with your design you could just use

CREATE TABLE IF NOT EXISTS tablename ( ... )

Upvotes: 2

Related Questions