Sunday, February 19, 2012

Database design

I am creating database tables for company testimonials. Database columns: name, position, companyname, comment, service we provided.

My question is that for each company - may have a multitude of different services from us, and different people with different positions in the same company may make comments.

What is best practice for putting this db structure together?

Thanks

Andrew

You have 4 principal objects: Company, Person, Service and Testimonial. That leads to 4 tables:

Company
CompanyIDPK
Address1
Address2
etc...

Person
PersonIDPK
Title
Initials
FirstName
Surname
Position
CompanyIDFK
etc...

Services
ServiceID
Service

Testimonials
TestimonialIDPK
Testimonial
PersonIDFK
ServiceIDFK

So now each testimonial is linked to a person and a service, and each person is linked to a company, so a testimonial is linked to a company through the person. You can add another table:

CompanyServices
CompanyIDFK
ServiceIDFK

to manage the relationship between companies and all the services they avail themselves of, regardless of whether they comment on it.

No comments:

Post a Comment