Creating an SQL Table with Multi-Value Fields

[tammic]

Has anyone had any experience creating SQL tables with multi-value fields? I have a Prime legacy system and am trying to import data but the files in Prime have fields that are multi-valued. I have a feeling this is going to be tough but someone might have experience in doing this?

 

[JayKusch]

please explain 'multi-valued'.

Thanks

J. Kusch

 

[tammic]

Multi-value means more than one value can appear in one field. For example, if you had a customer table and there were more than one contact. Mr. Smith and Mr. Jones are both contacts for customer ABC.

 

[JayKusch]

You would need a "step table" or another phase would be a lookup table.

Your tables "may" look like this:

tblCustomer:

CustID CustDescr CustContactID
------ ---------- -------------
100 CustABC 100
101 CustDEF 102
102 CustGHI 103

tblContact:

ContactID CustName CustAddress1 .....
--------- -------- ------------
100 Mr. Smith 123 Iway
100 Mr. Jones 456 Left Prkway
102 Mrs. Smith 12 Windy Way
103 Ms. Tater 101 Tot Dr.

Then you do a join on the tblCustomer to the tblContact on the tblCustomer.CustContactID = tblContact.ContactID




Thanks

J. Kusch