Adding Records and Table Organization

[rd81]

I'm really new to all this, and I had a question about how I should setup my tables.

I have a large amount of data that I have split into three categories.

Etch Data
Pollish Data
Test Data

Each Roll that comes of our lines has a Roll Number. Each Roll has data from those 3 categories. The user should be able to enter data for each category independantly. I made four tables tblRolls, tblEtchdata, tblPollishData, and tblTestData.

Each table has a field called RollNumber which is each table's primary key. If I create a new record in tblRolls, I want to create a new record in the other 3 tables with the rollnumber filled in. I was wondering how to do that and if the way I have my tables organized is the best way.

tblRolls
-- RollNumber

tblEtchData
-- RollNumber
-- 5 other etch fields

tblPollishData
-- RollNumber
-- 5 other pollish fields

tblTest Data
-- RollNumber
-- 5 other test fields

Help.. Thanks.

 

[lespaul]

depends...have you read 'The Fundamentals of Relational Database Design'? It's a must read for anyone designing databases.

My first question would be, what kind of relationship exists between tblRolls.RollNumber and the other tables? Is it a one-to-one? one-to-many? many-to-many?

What are the '5 other xxx fields'? If they are anything like: EtchDate1, EtchDate2, EtchDate3 - then your tables are not normalized!!

Do you really want to enter test data as soon as the roll is entered? What happens if somewhere in the process that roll is scrapped? Now you have test record information for something that really doesn't exist. Shouldn't you wait until the roll begins the test process before entering any information about the test?



Leslie

 

[willir]

rd11

What is the nature of the database -- why do you need it, what type of information do want to get from it, what type of reports are you looking for?

Tell us a bit about your business. What is etched or polished?

Leslie is bang on when she comments about Etch1, Etch2 type of thing. And she provided you with an excellen link to a reference document.

Look at a simple contact database...
You have people. But you wont create different tables for each type of person - one for sales, on tech, on for management. Instead, you would have the contact table and as part of the table, include their title or skill.

You would however, create a new table for the cars they drive - cars are very different than people. But we would like the contacts / people to the car.

Hope this helps
Richard

 

[DanJR]

But you wont create different tables for each type of person"

I begg to differ
Here are two senario's that may end up with a e.g. Managers 'table':
1. there are attributes that are functionally dependant (FD) on manager.
2. managers participate in other relationships

After formulating a relational model, there may also be physical characteristics that support a managers table e.g. the ratio of managers to people is small and a large amount of quering is performed on managers (not a good example in this case).

My point being that I can't see why people are different from cars when it comes to modelling.

Cheers,
Dan

 

[willir]

Hi Dan

Perhaps a "manager table" is required in some situations. But, I find that self-join works in many cases.

As for joins of managers to other tables, again, it depends, but I don't see a reason why an EmployeeID can not be used to join to other tables.

What I do think is that...
- If you want to search for contact or detail info, it is much easier to search on one table rather than mutliple tables.
- It tends to be much easier to develop an application with fewer tables.
- You want to avoid duplication of data on two tables.
- I feel the first two rules for normalization support my presepctive...
-- Columns / fields are atomic, only one value exists and can not be broken down further. No arrays, no sequence - eg. Type1, Type2...
-- Unique identifier (primary key) and all relavent data is dependent on the primary key. Example, Employee name is dependent on the EmployeeID.

If you are going to have a "manager's table", it probably should not include the name of the manger, phone number, etc.

But as stated, the real solution will depend on the desired outcome and objectives. And yes, this includes the common sense things that require us to break the rules of normalization to accommodate / fascilatate process.

This is of course just my opinion.
Richard