First, let me say, I'm very green at this... My database experience has been limited to mere flatfiles with MS Works (Laugh if you wish, I understand.)

I'm at the point now, where I am about to embark on a redesign of this flatfile (migrating to a relational structure)... I've got a long road ahead, to be sure. But I think I've got all the pieces in place, the concepts, and plenty of resources to rely on to get the job done.

I'm in the planning phase and the first table(s) I go to make brings up a situation I'm not sure how to proceed. (My first step and I trip already.)

Here's the situation. The db ultimately is going to be an order-entry system; customers, jobs, etc.

Some customers are Companies... some customers are individuals working for companies (more than one individual at company, different teams, etc.)... and some customers are just individuals.

So the question is how do I build the tables?
ID, CompanyName, Address, phone, etc.
ID, IndividualName, CompanyID, Ext, whatever?
ID, IndividualName, Address, phone, etc.

The solution is right on the periphery of my thought process... I can almost see it.

Any suggestions?

TIA,
~Woody

The proper way to model this, though it can be a bit difficult to get your head around, is to represent both companies and individuals in a single table which I typically call "Entity". You can relate entities to each other as employer/employee relationships, company/division relationships, company/employee relationships, and owner/company relationships. Some comples constraints need to be created to ensure referential integrity, but I have found this to be the most robust schema for representing this logical data model.

Yeah... this is kind of where I'm heading to... I think I may have it (or at least the basic of it).

1 Client (ClientID, ClientName, AddressID)

2 Individual (IndividualID, ClientID, LastName, FirstName, CompanyID)

3 Company (CompanyID, ClientID, CompanyName)

4 Address (AddressID, Address, Address2, City, State, etc.)

Where each of the first items are the primary key... ClientName is the Individual's Name(s) unless they are part of a company, then it's the CompanyName with the individual as a contact.

I think that's right. (Swear to goodness I don't know why they call it normalizing... it ain't normal.)

~W

Here's a good template you should take a look at:
A Universal Person and Organization Data Model

Thanks dportas,

I started to read it and then realized I was running down the clock at work. I'll try to finish reading it tonight... (no O/T)

But what I gleaned so far is showing me I might be on the right track.

Thanks again.

~W

I haven't decided yet, whether "I got it" or that I'm on the verge of epiphany.

I've got three tables planned to create a "customer" entity ("customer" is what will be shown on the top of the form... basic information only, I'll worry about the other stuff (phones etc) later)

So I have my first table:

customer (customerID, customername)

Seeing how I have basically two types of customers; companies and people that suggests two more tables, as well as adding an additional boolean field to my first (this will help direct things as to which table to enter the data to.)

customer (customerID, customername, boolean(company y/n?)
company (companyname)
person (last,first,mi)

later on down the line, when I get to the point where the customer specifies a contact (either a company has a contact name, or a person names another as secondary contact) I can create an association table that links the two.

associate (customerID, person)

That sound about right?

~W

You are missing the point. What I termed "Entity" is what the article refers to as "Party".
Your logical data model will separate businesses and individuals, but your physical data model will combine them into a single table.
I avoid 1-1 subtype tables whenever possible, in favor of more generic fields and nullable columns.

Hmmm... I thought I got it. I guess I'm mistaken. Back to the white board.

I'm not quite sure I follow. Are you referring to my "associates" table? Or just the simplicity of the tables? ex. Company(CompanyID, CompanyName)

What you're attempting to model here is something known as a supertype/subtype relationship. Here's an article about it you might find useful:
Implementing Table Inheritance in SQL Server - SQLTeam.com

So you'd end up with tables like this:
party(party_id*, party_type)
person(party_id*, party_type, first_name, last_name)
company(party_id*, party_type, company_name)

What blindman is talking about is collapsing this into one table:
party(party_id*, party_type, first_name, last_name, company_name)

As I see it, there are benefits and drawbacks either way, so it's up to you which method to use.

Thanks Futurity and Blindman... Looking at my notes and whiteboard... I may be off the path... but only by a step or two...

I'm going to keep plodding along and play around with this...

Expect me back...

...which is the type of 1-1 relationship I specifically try to avoid.

Well, I can see I'm a ways off, and that there are different schools of thought. I'm chuckling to myself how diametrically opposed those two statements are if read on their own. (ie. If I'm attempting to drive a car to the store, I wouldn't avoid actually driving the car.)

Off to study...

Conceptually the concept is the same, regardless of how one physically implements it in the database, no?

That depends. Is your objective to drive the car, or is your objective to get to the store?