[thelist] Newbie - DB Design Help

[Ken Schaefer]

Hi,

I disagree with Rob's proposal, because I think you will have data
integrity issues.

Suppose you have a service called "ServiceA" and your record looks like this:

ServiceID:     1
CompanyID:  1   (foreign key to Companies.CompanyID)
ServiceName: ServiceA

What happens when you delete Company1 from the Companies Table? You
would then have a referential integrity problem. You would also need
to delete ServiceA from your Services table. But then you have no
record that ServiceA exists.

Maybe in your system that's not a problem. Maybe it is.

The correct way to design a database to do up an ER model
(Entity:Relationship model). Generally your entities will become
tables. Where entities have a M:N (Many:Many) relationship, you will
need a "bridging" table. If you do up your ER model first, your
database schema (at least in 3rd normal form) writes itself.

Cheers
Ken


On 7/26/06, Todd Richards <todd at promisingsites.com> wrote:
> Rob -
>
> Thanks for your input, and the option outlined below.  I appreciate your
> feedback!
>
> Todd
>
>
> -----Original Message-----
> From: Rob Smith [mailto:rob.smith at lexjet.com]
> Sent: Wednesday, July 26, 2006 7:33 AM
> To: todd at promisingsites.com; thelist at lists.evolt.org
> Subject: RE: [thelist] Newbie - DB Design Help
>
> <snip>
> In this site, a list of services each company provides will be labled by
> level of expertise (let's call it rookie, modertate, expert).
> </snip>
>
> One of the cool things about building databases is the fact that you can
> design it through using plain English. Obviously you have a one-to-many and
> possibly a many-to-many relationship. Meaning, Company A may have a generic
> service that Company B, might use. It wouldn't makes sense to create an
> entirely new Service for company B, but share the two, whereas Company A
> ONLY does a particular service that makes them unique to the industry.
>
> Having bridging tables is good for a great deal amount of information.
> For proper relational database management you're on the right track.
> You could also get away with the following without using a bridging table.
>
> Table "company" (for all of the company information; obviously not all of
> it)
>    - company_ID
>    - company_Name
>    - ...
>
> Table "services" (for the list of the various services)
>    - services_ID
>    - company_ID  (as a FK)
>    - services_Name
>    - services_Description
>
> Table "level" (for the 3-4 levels of expertise)
>    - service_level_ID
>    - services_ID (as a FK)
>    - service_level_Name
>    - service_level_Value (1, 2, 3, etc)
>
>
> Rob Smith
> LexJet
> rob.smith at lexjet.com
> http://www.lexjet.com
> (800)453-9538
> (941)330-1210 Int'l
> (941)330-1220 Fax
> 1680 Fruitville Road, 3rd Floor
> Sarasota, FL 34236
>
>
>
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>