I am very new to teradata and cannot find the proper way to create my database. I have an Access database and migrating the tables and relationships to teradata and leaving the front-end in Access for end user usage. I have created my tables with the create table SQL and noted my primary keys in the tables. I have a main table that uses data from all my other tables. Example
program table, state table, product table (there are more than just this) then a qualmain table.
The tables have data that never changes. The changes are only in the qualmain table. So, there is a PK in the program table and then prog_nm and prog_desc. The PK is prog_id and that is in my qualmain table. It is the same scenario with my other tables. So if I were to query I have a relationship between prog_id in program table and prog_id in qualmain. I hope this is making sense. This way all my queries and forms perform the way they should and it all works great in Access. But, I need a way for users to use my front-end and it connect to a backend and this is why they want me to migrate to Teradata.
So.......being a complete newbie to teradata and knowing SQL queries, I am not sure how to set up relationships within the tables. I am thinking that is where index comes in but cannot find any help documents in terastudio that make any bit of sense. It is a bit vague. Then I cannot find any example on how I build a relationship or in this case an index. Can someone provide some links to help me understand what I am to do so my end result is creating an ODBC so I can use the front-end in Access in conjunction with the teradata backend? I am assuming I leave my queries in Access as well. My VBA code I assume stays in the Access front-end too.
I was informed I don't need an index, however it might be a good idea to collect statistics on the tables and columns to allow for better performance. So, trying to figure this out.
You already have indexes :-)
If you define a PK in the Create Table Teradata will implement it as an Index, a Unique Primary Index (UPI).
You can keep the PK/UPI for your other tables (which are probably way smaller than the qualmain), but for the qualmain there might be a better performing Primary Index (depending on the size of the table it might be ok, too).
Everything you'll ever need (not only) for defining indexes can be found in the big Teradata Database Design manual,
Regarding stats collection you might submit "diagnostic helpstats on for session;" and then Explain your queries, at the end of the Explain there will be a list of recommended statistics.
And you should read Carrie's blogs on stats, e.g. Recommended Dictionary Statistics to Collect in Teradata 14.0