Many tables or many databases?

Database

Many tables or many databases?

For a project, we have several groups that will produce numbers using models. Input and output data will be in the DB. Each group is independent and works in their own different way; however, the outputs they produce will need to be combined, aggregated and queried. 

I am debating whether to create one database with a lot of tables or many smaller databases which belong to each group. I am leaning towards creating multiple databses because of easier access management. However, I am concerned about performance.

Will querying tables across databases affect Teradata performance? 

Thank you!

2 REPLIES
Enthusiast

Re: Many tables or many databases?

If you go the route of one database, based upon your description, you'll be forcing the dba team to maintain object level permissions, never a good idea.  If you create a db for each team, your security paradigm is much simpler.

Creating a db for each team will allow them the flexibility to alter their own schema without impacting any of the other teams.  Combining, Aggregating and Querying their "common" results will not result in performance degradation with a few caveats, make sure the "output" table DDL is as close to identical as possible to reduce the amount of processing in the "combined" view, ensure that every "output" table has the same Primary Index, NUPI or UPI doesn't matter, if you assign them all the same PI, you'll be designing in AMP local work, which is where Teradata shines.

Set up an aggregate view in a reporting/analysis db separate from the specific team db's and grant select on the base view databases for each individual team.

Have Fun!

Enthusiast

Re: Many tables or many databases?

Hi,

I feel you need to look at things from different perspectives like, short term, long term plans.

Will the group expand or shrink? How the data volume is going to grow? Think from the standpoint of business too etc etc.

I am more inclined towards creating DB for each team to be more flexible in future and you can control easily of any changes.You can also classify them as Input and Output for ease of management.

Cheers,

Raja