Guidence setting up table scheme

[Jules Marchildon]

Hi There,

Hoping someone can give me some constructive criticism with the table scheme below.  This is my first DB SQL attempt, ... I have done some light reading, and some light 'Learning 101" playing around with SQLite, and SQLightening and I have settled on moving forward with SQLightening although my beginnings will be running local mode for this project,  later, I want to work towards a remote mode application for a different project.

The project I want to create is sort of a light weight CRM program  I can take with me on my laptop and capture my everyday data in an organized way.  I will be Importing/Exporting csv files from/to our mainframe,  I want to print a contact booklet I can keep in my pocket,  create an 'at a glance' progress charts among a few other crazy things.

Outside of working with existing customers and developing new customers, my day consist of working with other dept, such as inside sales, branch GM, Sales Mngr, Regional Mngr, product managers and outside people that are the local mfg reps, and  local Factory Sales Engineers.

Again, the DB and SQL concepts and terminology are new to me, so I hope who ever lends a helping hand has  lots of patients!   

Thanks in advance!
Here we go....


Company table:
  Company ID (PK)
  company name
  company street number
  company Street address 1
  company street address 2
  company town/city
  company province/state
  company country
  company phone
  company fax
  company website
  company logo bmp       <-cut and paste into GUI target(+) from screen shot snippet
  company category       <-from pick list table
  company market segment <-from pick list table
  company note

Contact table:
  Contact ID (PK)
  Company ID (FK)
  contact Salutation  <- from GUI pick list, Mr. Mrs. Ms.
  contact Last Name
  contact First Name
  contact Title       <- from GUI pick list, VP, DE, CE, BU etc.
  contact Email address
  contact Mobile Number
  contact Office Number & Extension
  contact note   

Sales Visit table:
  Sales Visit ID (PK)
  Company ID (FK)
  Opportunity ID (FK)
  Document List ID (FK)
  sales visit date 
  sales visit objective <-WTC, WTD, WTO, KTC
  sales visit strategy  <-Numerical, broadening, Image
  sales visit note

Opportunity table:        <- aka company term Nugget = strategic sales opportunity
  Opportunity ID (PK)
  Company ID (FK)
  Contact ID (FK)         
  opportunity project name
  opportunity phase       <- concept, design, sampling, proto-type, pre-production, production
  opportunity mfg
  opportunity PN/Type/Desc
  opportunity EAU
  opportunity ASP
  opportunity part status <- in process, won, lost, cancelled
  opportunity sampled     <- Yes, No
  opportunity quoted      <- Yes, No
  opportunity note

Document List table:      <-place to save all related eDocuments
Document List ID (PK)      could be mfg spec sheet, quote, cust product offering etc
Opportunity ID (FK)        or could be a weblink to something
document file       

Category table:           <-Pick List
  Category ID (PK)
  category type [Cat data]    

Market Segment table:     <-Pick List
  market segment ID (PK)
  market segment type [MS data]               
  market segment desc [MS data]
   

'-------------------------------------
[Cat data] 1 column
Customer
Manufactures Representative
Supplier/Factory
Distributor
Broker
           
[MS data] 2 columns
A-0 Transportation       
A-1 Automotive Wiring Harness     
A-2 Automotive OEM/Device     
A-3 CV Wiring Harness       
A-4 CV OEM/Device       
A-5 Contract Mfg       
A-6 Other       
         
1-0 Business Machines 
1-1 Security 
1-2 Retail Equipment   
1-3 Business Equipment
1-4 Education Equipment 
1-5 Other Equipment 

2-0 Communications
2-1 Telecom 
2-2 Lan/Wan
2-3 Cell/Consumer Phones 
2-4 RF/Microwave 
2-5 Other Communications 

3-0 Computer
3-1 MF/Mini/Super Comp
3-2 PC'S, Workstations
3-3 CAE, CAD, CAM, Graphics Imaging
3-4 Storage
3-5 Peripherals
3-6 Other Computers
       
4-0 Contract Mfg 
4-1 Low/Med Volume 
4-2 High Volume 
4-3 NPI Center 
4-4 Military 
4-5 Cable Assembly     
4-6 [see A-5]     
     
5-0 Consumer 
5-1 Audio/Video/Games 
5-2 Appliances 
5-3 [see A-codes] 
5-4 Other Consumer 

6-0 Military Aerospace
6-1 Comm Avion/Marine Elect
6-2 Military Avionics/Space
6-3 Guidance/Control/Navig
6-4 Electronic Welfare
6-5 Military/Gov Bases
6-6 Communications
6-7 Other Military

7-0 Indust/Instrument 
7-1 Process Controls 
7-2 Instrumentation 
7-3 Prod Equipment 
7-4 Test & Measurement       
7-5 Power Supplies       
7-6 Other Industrial

8-0 Medical 
8-1 Test & Measurement   
8-2 Life Support 
8-3 Other Medical 

9-0 Component Reseller
9-1 Auth Distributor
9-2 Exporter
9-3 Unautho/Broker

[Rick Kelly]

Without knowing your requirements, I noticed two things that I'm in the habit of doing differently - addresses and phone numbers. I usually keep them in separate tables and allow a 1-many relationship between them and person/organizations. Most of the time I also have inactivate dates on them so it's possible to either note a future address/phone number change or, for the support of "seasonal" address/phone numbers.

Rick

[Jules Marchildon]

Thanks for peeking Rick,

I'm having trouble Data Modeling  the Document List.... more ore less setting up the relationship ( I think?) 
( not sure if this is in the same context as Schema Data? )

I will probably need to save at  least 1 document, but I expect to have more... how many?,..  not sure...
my best guess could be 1 to 4... so  if I make one reference to the Document List ID in the Sales Visit  table then I should add more columns to the Document table? Is this the right direction?

Document List table:           <-place to save all related eDocuments
    Document List ID (PK)       could be mfg spec sheet, quote, cust product offering etc
    Opportunity ID (FK)             or could be a weblink to something
    document file 1
    document file 2
    document file 3
    dcoument file 4

Boy, at this stage I guess,.. this is really where you need to be the expert, I mean setting up tables now and later finding out it doesn't work out once I start  digging into SQL to retreive stuff...

Thx.
Regards,
Jules

[Fred Meier]

I would consider have a unique row for each document.  This would also allow for additional data.

Document List table:           <-place to save all related eDocuments
    Document List ID (PK)       could be mfg spec sheet, quote, cust product offering etc
    Opportunity ID (FK)             or could be a weblink to something
    Date
    Notes
    Extension                   Would identify type of document (Doc,Xml,Xls,Pdf) and allow you to shell to viewers.
    Document                    Contains the compressed file data