Creating Relationships 1

[CUJO2001]

I am new to this game so please bear with me. I am a web developer and I am creating asp pages and I need a little help with the database. I want to enter data into 4 tables and have it pull from each one to save time. (i.e. table one have client info, table two has purchase info that relates to client, table 3 has financing info that pulls data from 1 & 2 plus its own info) As you can see I am LOST. I have tried but I need HELP. SOMEONE SAVE ME. Thanks....

John Gelsinger
Killeen, TX

 

[kathryn]

John,

Could you tell us what help you need? Do you need help designing the tables, or setting up the relationships?


Kathryn

 

[CUJO2001]

I have the tables established. I want to pull data from all of them to create one table or is it easier to use just the one table. There is 129 data fields all together. i.e i have a id number in the first table. I want the number in all tables, as well as the information I will add later. I need to know can I enter data into one source and have it ent up in more than one table? If so HOW? I know I would have to do relationsips but I am lost. Thanks.

John

 

[JerryDennison]

I suggest you start with normalizing your database structure. I won't go into the Forms of Data Normalization here but to have a robust and efficient db it should adhere to the first Three forms. Build your db on paper first. To do this write down each piece of information you need to track and be very descriptive. Here's an example:

customer first name
customer last name
customer phone number
...

customer sales order number
customer sales order date
customer sales order salesperson
...

customer sales order detail item
customer sales order detail quantity
...

vendor name
vendor address
vendor phone
vendor contact
...

vendor product id
vendor product description
vendor product cost
...

salesperson first name
salesperson last name
salesperson commision rate
...

You get the picture.

There are two ideas you should understand. Entities and Descriptors. Think of an entity as something you can hold in your hand(if your hand were big enough). In most cases an entity is a physical object(although it can be an intangible). A descriptor is something that describes the entity and only the entity. In my example above you have six different entities emerging from our exercise. Customer, Customer Sales Order, Customer Sales Order Detail, Vendor, Vendor Product, and Salesperson. You'll note that some entities appear to be RELATED to other entities. That is the entity itself is a descriptor for another entity. Anytime a word is repeated more than once it should be considered an entity. So for example: vendor is an entity AND product is an entity. In this case product is said to be related to vendor. Taking this a step farther, since we intuitively know that a vendor may sell many products the relationship between the two is considered one-to-many.

Only when you have a sound structure can you add to and retrieve your data in an efficient manner.