Every database starts out as an entity-relationship diagram. In order to model a business process, the developers of an application must first map out the different components of a system. This map of a business process is often referred to as the entity-relationship diagram, or ERD for short. The ERD consists of two different components,
Entity A person, place, thing, or idea involved in the business
Relationship The ties that bind entities together
An entity-relationship diagram is helpful to understand the process flow of data through the system. Once an entity-relationship diagram is created, the developer must then create a special diagram that models the data stored in a database to represent the entities and relationships in the ERD. The name of this special diagram is "logical data model," or LDM for short. The LDM will be used to display how all data relating to the business process being modeled is stored in the database. A logical data model consists of a diagrammatic representation of tables in a database.
In order to model data, there must be relationships between the various components that make up a database design. These components are stored as data, while the relationships between data can be defined explicitly via the use of integrity constraints and/or database triggers that model business rules, or implicitly by the data manipulation statements that select data for viewing or populate the database with new data.
The first step in creating a list of table columns is to determine what will be the unique characteristic of any row in the table. THis is called as primary key. The determination about whether to incorporate data as a column into the table should rest on two conditions:
1. Is this data functionally dependent on the primary key?
2. Will there be only one copy of this data per appearance of the
Each column that is not part of the primary key is considered to be "functionally dependent" on the primary key. This term simply means that the dependent column stores data that relates directly to or modifies directly each individual row.
Once these factors are determined, the designer will know if he should include the column in the table or whether the column should be used to define another table. Data components that have no functional dependency to the other data in a table record should be placed in separate tables.
Foreign-key relationship is often referred to as a parent/child relationship because of where the data must appear in each table to create the foreign-key relationship. In the "child" table, the data can appear either as part of the primary key or as a functionally dependent column. However, in the "parent" table, the referenced column must appear in the primary key.
I would recommend visiting a bookstore or library. A small investment in a book about database design will probably go a long way for you. You don't even need one specific to Oracle or any other database brand.