Schema set up

[dreampolice]

Here is what I have for creating a Schema:

- Point of Contact Name
- Point of Contact Phone
- Expert Name
- Expert Phone
- Project Name
- Project Location

On some occasions the Point of Contact Name will be the same person as the Expert Name.

For the Schema I am looking at this:

ContactTable
contact_id -> primary key
firstname -> text data type
lastname -> text data type
phone -> number data type
poc -> boolean data type
expert -> boolean data type

ProjectTable
project_id -> primary key
contact_id -> foreign key
projectName -> text data type
projectLocation -> text data type

Does this sound like the correct direction for this Schema?

 

[maxhugen]

Looks OK basically, I think. A couple of thoughts:

1. Sounds like a Project could have either or both POC and Expert... in which case you may want to have two fields like 'POC_ID' and 'Expert_ID' (both FKs linked to ContactTable) in ProjectTable.

2. The Phone field in ProjectTable is numeric... I'd use Text, so users can enter '312 456 3333' etc?

Max Hugen
Australia

 

[lespaul]

ditto the phone number comment...as a rule of thumb, if you aren't going to be adding it up, it shouldn't be a numeric field. For example, Social Security Number, Zip Codes, Phone Number, etc...

Shouldn't the Point of Contact and Expert be fields in the PROJECT table, not the contact table? Let's say I'm a contact and I'm an Expert on one Project and the POC on another...I think that those fields should be moved.



Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[dreampolice]

Thanks, I changed it in my Access 2003 database as you suggested.

The form entry will have an area for Point of Contact and another form entry for Expert.

So it would look like this in my Web Front end:

POC firstname: <input name="pocFirstname"....>
POC lastname: <input name="pocLastname"....>
POC Phone: <input name="pocPhone"....>
Expert firstname: <input name="expertFirstname"....>
Expert lastname: <input name="expertLastname"....>
Expert Phone: <input name="expertPhone"....>
....

How would I get the Contact Table insert to work in SQL.
I assume I would need 2 insert statements?

INSERT INTO ContactTable
(firstname, lastname, phone)
VALUES
('form.pocFirstname', 'form.pocLastname', 'form.pocPhone');

INSERT INTO ContactTable
(firstname, lastname, phone)
VALUES
('form.expertFirstname', 'form.expertLastname', 'form.expertPhone');

Insert into ProjectTable.....

 

[maxhugen]

Leslie, it looks like a Contact is 'tagged' as POC &/or Expert, which is perhaps why those fields are in the ContactTable?

DreamPolice, I don't know much about Access over the Web... but if you have a web page that brings up details of a Project so you can add details for 2 contacts (the POC and the Expert), then I'd assume you would have to run 2 insert statements.

However, in terms of schema, you can only assign one Contact to your Project (field contact_id in ProjectTable). I'm unclear as to how you're structuring this. Which is correct? :

- One Contact can have many Projects
- One Project can have many Contacts
- Many Projects can have many Contacts

Max Hugen
Australia