A table is two dimensional data matrix i.e. it’s the combination of rows and columns. The rows contain the data record and columns contain the field.Tables can be containing zero or multiple records or rows.
Ex: Employee Table.
Types of Tables
1. Client Dependent
2. Client Independent
Client Dependent:- If the first field of a table is MANDT and data type is CLNT then it is called client dependent table. The length will be always 3.
Client Independent: - If the first field contains other than the MANDT then it is called client independent table .
The client dependent tables are again of three types.
1. Transparent tables.
2. Pooled Tables.
3. Clustered Table.
1.Transparent Tables
A transparent table in the dictionary has one to one relationship with a table in the database. Its structure in R/3 Data dictionary corresponding to a single database table. The database table has the same name, the same number of fields and the fields have the same names as the R/3 table definition. It contains application data. In below fig first part will shows tranasparent table.
Table Pools and Pooled Tables
A pooled table in R/3 has a many-to-one relationship with a table in the database. For one table in the database, there are many tables in the R/3 Data Dictionary. The table in the database has a different name than the tables in the DDIC, it has a different number of fields, and the fields have different names as well. Pooled tables are an SAP proprietary construct.
In the database pooled tables are stored in a single table called a table pool table.R/3 uses table pools to hold a large number (tens to thousands) of very small tables (about 10 to 100 rows each). Table pools reduce the amount of database resources needed when many small tables have to be open at the same time. Pooled tables are primarily used by SAP to hold customizing data. In the above fig second part is an example for pooled tables
Table Clusters and Cluster Tables
A cluster table is similar to a pooled table. It has a many to one relationship with a table in the database. Many cluster tables are stored in a single table in the database called a table cluster.
Table clusters are used to hold data from a few (approximately 2 to 10) very large tables. They would be used when these tables have a part of their primary keys in common, and if the data in these tables are all accessed simultaneously. Table clusters contain fewer tables than table pools and, unlike table pools, the primary key of each table within the table cluster begins with the same field or fields. In the above fig third part is an example for cluster tables.
Restrictions on Pooled and Cluster Tables
A pooled table in R/3 has a many-to-one relationship with a table in the database. For one table in the database, there are many tables in the R/3 Data Dictionary. The table in the database has a different name than the tables in the DDIC, it has a different number of fields, and the fields have different names as well. Pooled tables are an SAP proprietary construct.
In the database pooled tables are stored in a single table called a table pool table.R/3 uses table pools to hold a large number (tens to thousands) of very small tables (about 10 to 100 rows each). Table pools reduce the amount of database resources needed when many small tables have to be open at the same time. Pooled tables are primarily used by SAP to hold customizing data. In the above fig second part is an example for pooled tables
Table Clusters and Cluster Tables
A cluster table is similar to a pooled table. It has a many to one relationship with a table in the database. Many cluster tables are stored in a single table in the database called a table cluster.
Table clusters are used to hold data from a few (approximately 2 to 10) very large tables. They would be used when these tables have a part of their primary keys in common, and if the data in these tables are all accessed simultaneously. Table clusters contain fewer tables than table pools and, unlike table pools, the primary key of each table within the table cluster begins with the same field or fields. In the above fig third part is an example for cluster tables.
Restrictions on Pooled and Cluster Tables
Pooled and cluster tables are usually used only by SAP and not used by customers, probably because of the proprietary format of these tables within the database and because of technical restrictions placed upon their use within ABAP/4 programs. On a pooled or cluster table:
Secondary indexes cannot be created.
1. You cannot use the ABAP/4 constructs select distinct or group by.
2. You cannot use native SQL.
3. You cannot specify field names after the order by clause. order by primary key is the only permitted variation.
Because of these restrictions on pooled and cluster tables and because of their limited usefulness. Normally in real time system we will not create much pooled and cluster tables. Only transparent tables are used or created maximum. So we will discuss only about transparent tables
Other Components of the Table
Fields: - Fields are nothing but columns.
Domain: - Domain consists of the technical characteristics of a field such as field length and data type.
Data Element: - A table is composed of fields to create a field you need a data element. The data element contains the field labels and documentation (F1 help) for the field. It contains the semantic characteristics for the field and it works like a interface between Field and Domain.
Domain and data elements are reusable. A domain can be used in more than one data element and data elements can be used in more than one field and in more than one table.
Delivery Class:- Delivery class comes under attributes. The value in the delivery class field identifies the “OWNER” of the data in this table. The owner is responsible for maintaining the table contents. In customer tables we always enter ‘A’ Here which indicates that the table contains application data owned by the customer only.
Ex: A – Application table (Master and transaction data).
Data Class: It comes under technical settings. It defines the physical address of the database in which the table uses creates and logically stored or it’s a physical place where the actual data is to be stored
Categories of Data Class: APPLO- Master Data, transparent tables.
Size Category: It also comes under technical settings. It defines the probable space requirement for a table in the database.
Categories:
0:- 0 to 30,000.
1:- 30,000 to 1, 20,000.
2:- 1, 20,000 to 4, 90,000.
3:- 4, 90,000 to 1,9,00,000
Table Maintenance allowed: Its also comes under attributes. By enabling table maintenance allowed user can be able to enter the data, change and display manually.
Approaches for creating tables.
There are two approaches you can use when creating tables.
Top-down-approach: In top down approach first we create the field then data element then domain.
Bottom-up-approach: In the bottom-up-approach first we create the domain, then data element and then field.
Direct Method: Do not have data element or domain.
Primary key: Primary key is a field or combination of fields that uniquely identify a row in the database table.
Foreign Key: Foreign Key is a key which is a primary key of another table.
Naming convention for database tables:
Other Components of the Table
Fields: - Fields are nothing but columns.
Domain: - Domain consists of the technical characteristics of a field such as field length and data type.
Data Element: - A table is composed of fields to create a field you need a data element. The data element contains the field labels and documentation (F1 help) for the field. It contains the semantic characteristics for the field and it works like a interface between Field and Domain.
Domain and data elements are reusable. A domain can be used in more than one data element and data elements can be used in more than one field and in more than one table.
Delivery Class:- Delivery class comes under attributes. The value in the delivery class field identifies the “OWNER” of the data in this table. The owner is responsible for maintaining the table contents. In customer tables we always enter ‘A’ Here which indicates that the table contains application data owned by the customer only.
Ex: A – Application table (Master and transaction data).
Data Class: It comes under technical settings. It defines the physical address of the database in which the table uses creates and logically stored or it’s a physical place where the actual data is to be stored
Categories of Data Class: APPLO- Master Data, transparent tables.
Size Category: It also comes under technical settings. It defines the probable space requirement for a table in the database.
Categories:
0:- 0 to 30,000.
1:- 30,000 to 1, 20,000.
2:- 1, 20,000 to 4, 90,000.
3:- 4, 90,000 to 1,9,00,000
Table Maintenance allowed: Its also comes under attributes. By enabling table maintenance allowed user can be able to enter the data, change and display manually.
Approaches for creating tables.
There are two approaches you can use when creating tables.
Top-down-approach: In top down approach first we create the field then data element then domain.
Bottom-up-approach: In the bottom-up-approach first we create the domain, then data element and then field.
Direct Method: Do not have data element or domain.
Primary key: Primary key is a field or combination of fields that uniquely identify a row in the database table.
Foreign Key: Foreign Key is a key which is a primary key of another table.
Naming convention for database tables:
1. The tables we are creating are generally called as Z-tables or customizing tables.
2. The name of a table should be started with Y or Z that a user creates.
3. SAP has used A to X for its own use, Z or Y in the beginning means that the program or table is user defined.
So it avoids the redundancy between predefined and customizing tables.
