A very large table and a solution (1 Viewer)

[arishy]

I have a Contact Table that has data as huge as a contact in outlook ( many many fields).
I decided to split it into groups and hence separate tables
The Main table has the absolute minimum and also functional
ClientID (PK) auto
company name
First/last name
Mobile
Then followed by "other tables for:
ClientAddress
ClientContacts(all sorts of tel/fax/mobile/
ClientInternet ( web site email etc) .... you get the idea

My question: These are all one to one relation and as far as I research, it is NOT a good idea to use one to one. Why?

Since I am in the design stage , I hope to hear from you what a bag of "worms" I am creating for myself. I mean what potential problems I am going to face in such situation and advice on how to approach such a situation.

Your input will greatly appreciated

 

[spikepl]

I decided to split it into groups and hence separate tables

So you have decided to denormalize your data. Why?

 

[Cronk]

I also would ask 'why?'.

In my some years of developing quite a few applications, I have never had a situation where there was more than 255 fields; in fact, it has never come anywhere near this.

So all you do is to de-normalise and create more work for yourself.

How many fields do you have?

 

[arishy]

I got two "why"s .....That is good start ...I am on the wrong lane.!!!!

Outlook has 92 fields but nicely grouped ( Home, business etc)

So, I design ONE table and I pass rule number one in normalization and keep you both happy

How do I maintain such a huge table ??

I will design Data Entry forms for each "group" and make it easy for the user to pick the form that address a group ... fair enough.

Am I back to the correct lane ???

Is there is a sample data I can look at that address this issue ??

 

[Cronk]

Firstly, do you need all the fields that Outlook has for its contact entity?

If you do, then why not use Outlook to store your contacts?

I've never had a system which required more than a few telephone numbers, but it you did want to provide for so many, you would have a different structure such as
tblPhone
PhoneID
ContactID (FK into tblContacts)
PhoneNumber
PhoneTypeID (FK into tlkpPhoneType)

Similarly, I've never had need to store more that two different addresses, but if you did, then do the same for addresses.

 

[arishy]

Cronk, That what "the gurus" objected to......denormalizing.
The Phone data belongs to a primary key in the client so why put it in a separate table with ANOTHER PK......At least that what I understood from Rule #1 in Normalization.

 

[Cronk]

Just the opposite. What I am proposing with a table of phone numbers is normalising.

Consider an example where a system had to record all of the music CDs owned by several people.

You would not have all of the CD titles in one field in a tblCDs with a FK into the tblPeople.

If on the other hand I was designing a borrowing system where a person could borrow a maximum of 2 CDs, I would deviate from a pure relational and have 2 fields for the CDs in the Person table.

Incidentally, I would not be including a phone number in a primary key. Make the phone field unique instead.

 

[arishy]

Cronk,
I am trying "hard" to follow your logic...so,
TableCD will have one record for each cd and FK for the owner
TableOwner is again one record per owner. They can own several CD's
There is one to many relation bet TableCD and TableOwner.
How this relates to my issue of splitting I do not follow you here....Keep on
Now Borrowers, we established a rule TWO CD's per borrower.
We will have table Borrower One per record, and it is "safe" to add TWO extra fields in for TWO FK's for the borrowed CD's. ( as you said , braking rules here).
I can live with such a design...but how this relates to my issue of splitting one entity to several tables ???

 

[Cronk]

The example I gave with the separate table of CDs is a one (Person) to many (CDs) relationship. And this is the right way to set up such a database.

What I'm saying, I might "break the rules" of a proper relational database (and have the 2 CD fields) if I know the data model has such a small number.

You originally wrote My question: These are all one to one relation and as far as I research, it is NOT a good idea to use one to one. Why?

What you are having is a one to many relationship.

 

[arishy]

I did not doubt your design at all, and just to comfort you. m$ did it BIG SCALE when allowed in Access the multi value field. They blew Rule #1 to pieces. And all that for the sake of marketing.

Please explain to me how ONE Entity (The Contact ) become One To Many by splitting the Entity into say two tables.

tblContantName:
ContactNameID PK
Name


tblContactPhone
PhoneID PK
Phone
ContactNameID FK

Original table
tblContact:
ContactID PK
Name
Phone

 

[Cronk]

If a contact has 6 phone numbers, there would be 6 records (many) in your tblContactPhone.

Incidentally, there would be a PhoneTypeID which would indicate the type of phone number
eg 1 = Home
2 = Work
etc

 

[arishy]

I see,
So Phone "group" table will be
PhonetID PK
Phone
PhoneType
ContactID FK

And if any group has the same characteristics, the many will happen again
Family members for example

tblFamily
FamilyID PK
memberName
memberRelation (1 Spouse etc)
ContactID FK

I will have a go, and thank you for your patience!!!!