[pnj]

I'm sure this thread will get moved to the technology area but more people look here then there so that's why I posted here..


I am taking a database class, we are using access 2007.

I understand a little about normalization but an stumped on the assignment we have. We need to put this into 3NF.

I have three tables, tblCustomer, tblContract and tblInvoice.
the fields in each, are;

tblCustomer
customerID (Primary Key)
company
firstname
lastname
phone
address
city
state
zip
email
comments

tblContracts
contractNum (PK)
customerID (FK)
contractType
signingDate
startDate


tblInvoice
InvoiceNum (PK)
contractNum (FK)
invoiceDate
InvoiceItem
InvoiceAmt
InvoicePaid


I don't see any fields that don't seem to fit the table they are in...

what am I missing?

 

[sanjuro]

you have contracttype listed twice. Typo?

 

[pnj]

yes. typo.. fix. thanks.

 

[S.n.a.k.e.]

Pnj,

Is this an advanced level class (given the language of choice I assume not)?

You could assert that phone, and address (Address, city, state, zip) are not 3NF because companies often have more than 1 phone and 1 address.

So you could create address tables (which require another table to identify what type of address (shipping, mailing, email etc), and phone tables (same thing there).

Also, on the invoice table, they only have 1 item per invoice? That speaks of an Invoice Detail table, which then opens the door for Inventory and....

Just my .02, YMMV, no warranties expressed or implied...

Marc

 

[ire]

Follow his advice....and slap your teacher for teaching a database class in Access.

 

[pnj]

It's not really an advanced class. It's the second of two classes but it's mostly Access 2007 and how to do simple stuff. We are just now getting into actually designing a DB. This current example is from the book..


I know what you are saying about the company possibly having multiple addresses. I don't think they would have a bunch of different addresses... I mean, we would be guessing about that because it's a fake company/business to begin with...

is that the only table (tblCustomer) that is not in 3NF? The other two tables seem OK to me...

what am I missing?

 

[S.n.a.k.e.]

Pnj,

For the customer table you could extend my ideas to the name fields as well as most customers have more than 1 contact, then you have a contacts type linking table between customer and contact, and then each contact could have more than one phone which fits in nicely with the normalization of the phones tables...

It gets pretty messy pretty fast once you start this. Then your queries all require stacked inner joins, and grouping data requires more thought... Slippery slopes man, slippery slopes.

Remember in 3NF, it's "The key, the whole key, and nothing but the key, so help me Codd"

Marc

 

[ire]

Until recently I did DB design and work for a living...trust me, companies have many addresses and many names. We always had multiple addresses and multiple doing business as (DBAs).

 

[sanjuro]

Yeah that sounds right.

Probably want to have a separate table with just customerid and company name, then a contact table with the customerid as the PK with addresses and names.

 

[pnj]

Ok, I'm back working on this thing today. I think my biggest problem is, I don't have enough knowledge of business stuff. Like multiple address, multi invoice items, multi contacts, etc...

because this isn't a real business, (it's just a class assignment) it's hard for me to think what all they may need. I also don't want to add more tables then I'd need....

I'll be back....

 

[pnj]

ok.. this sucks.

I'm ending up with a whole bunch of tables...

I have for instance;

tblCustomer
customerID
contactID
companyName

tblContact
contactID
firstName
LastName

tblPhone
phoneID
phoneTypeID
phoneNumber

tblPhoneType
phoneTypeID
phoneID
Cell
Mobile
Office
Home

*I have other tables but I have questions regarding these four right now..

Do I need a foreign key in each table for each other table?
I'm not sure how I would link, say, a customer with their contact name and phone number and or address, ect.. It seems like I'm going to have PK and FK for everytable, in everytable... I'm probably making this harder then it needs to be.

 

[ire]

Use joining tables. Let the customer table drive the structure, so you might have a table like this:

tblCustomer_tblPhone
customerID FK
phoneID FK

your query would look like this:

select tblCustomer.*
from tblCustomer,
tblCustomer_tblPhone,
tblPhone
where tblCustomer.customerID=tblCustomer_tblPhone.customerID
and tblPhone.phoneID = tblCustomer_tblPhone.phoneID

<edit>
the two FKs above make up the composite PK

 

[pnj]

ok, Joining tables.

so, in theory, for each table I may have, I'll probably have a joining table connecting it..?

so, each main table (such as tblCustomer) will I only have one PK or should I have multple keys in each table?

 

[S.n.a.k.e.]

PNj,

Ire has got it. They are called linking tables. The will have their own PK, and a couple of FK's to the tables they link. We generally call those tables Table1Table2Link.

So for Ire's example:
CustomerPhoneLink
CustomerPhoneLinkId PK
CustomerID FK
PhoneID FK

You then populate the main tables with FK's to the linking tables. As I stated before, it gets pretty messy, pretty fast. If this is a beginner level course, you might want to bring these ideas to the professor or TA and ask "How far do you want us to go?". They are trying to get you to think relationally and maybe going to the depth you have might be enough.

Ire, while the where clause method works, it is usually faster to do joins and makes the where clauses less cluttered...

E.G.

Select Customer.Company
,Contacts.First
,Contacts.Last
,Phones.PhoneNumb
...
...
From Customer
INNER JOIN Phones on Phones.PhoneId = Customer.phoneID
INNER JOIN contacts on Contacts.ContactId = Customer.ContactID
...
...
WHERE Contacts.FirstName = 'Phred' AND Phones.AreaCode = '800'

That query returns the set of customers who have an 800 number and contact named Phred and returns the company name and the contact info.

In true 3nf, you might have multiple contact types, (Main contact, Sales Contact, etc), multiple phones (Main, fax, tech support etc). There are so many books on this stuff and articles available on these topics... Best bet is to just steep yourself in how relational databases are created. Think about all kinds of stuff in this manner. It gets easier over time. Some days I still have trouble fully normalizing databases, and sometimes doing so isnt worth the work...

Again, just my .02

Cheers,
Marc

 

[pnj]

ok, back to basics...

we aren't supposed to add more fields, or not too many anyways. So I don't need to create a phoneType table and what not...

So...

tblContact
contactID (PK)
firstname
lastname

tblContactData
contactDataID
phone
address
city
state
zip
email
comments

tblCompany
companyID (PK)
CompanyName

tblContracts
contractID (PK)
contractType
signingDate
startDate


tblInvoice
InvoiceID (PK)
invoiceDate
InvoiceItem
InvoiceAmt
InvoicePaid

tblContact_tblCompany
contactID
companyID

tblContracts_tblInvoice
invoiceID
contractID

Something like that?
I realize it's not 3NF but given the class, am I somewhat on track?

Keep in mind, this is for a crappy Access class. I asked an instructor who teaches a different class them I'm taking and she was saying the first example I posted was fine.. (that's the example MY teacher wants us to change, so clearly not all these teachers are on the same page..)

thanks.

 

[$tinkle]

i still don't understand why tblContact & tblContactData aren't merged.

 

[pnj]

i still don't understand why tblContact & tblContactData aren't merged.

(me either...)

well, I had to change something in the DB so it looked like I did something, for the teachers sake. From what I've gathered, to put this small DB into 3NF, even without adding any new info (like multiple phone types, etc), I would still end up with many smaller tables... right?

would it make sense to have something like;

tblName
NameID
firstName
lastName

tblCompany
CompanyID
CompanyName

tblContact... this is where I start getting confused. I need to connect a Name to a Company.. so do I make a tblContact_tblCompany table with two primary keys so I can link to those tables?

*goes off feeling real dumb..

 

[johnbryanpeters]

Try adding tblInvoiceItems.

Don't complicate your life.