Initial "Customers" Table Planning

[justgeig]

I'm fairly new to the world of Access database programming. Been working through a very useful textbook on everything Access related and I think I'm ready to start making my initial tables and establishing relationships.

In my "Customers" table, there are fields for FirstName and LastName as well CompanyName. I'd like to make all of those fields required to avoid missing information. The problem I'm running into is that some of our customers are only consumers and have no company name. On the flip side, some of our customers are businesses that have multiple people that can place orders. Obviously, I can't make all the aforementioned fields required because some of our customers legitimately don't meet all the conditions. I'm guessing that I'm missing a basic cornerstone of database design but looking through my book and google hasn't produced anything of value. I'd really appreciate any suggestions.

[orange]

You might want to look at this tutorial
http://www.rogersaccesslibrary.com/T...lationship.zip

[justgeig]

I will try to be a little more specific.

In my "Customers" table, I want to ensure that either BOTH the "FirstName" and "LastName" fields are filled in OR the "Company" field is filled in for each record in the table.

Not every customer is a business and not every business has just a single person that we deal with. Unless I am missing something still, the link you provided (while another good read for me) wasn't useful for this specific problem.

[orange]

If they are all customers, then perhaps you could define Customers as an individual or Company with whom we do business. Then have a field to identify the type of Customer - individual/business.

And a check within your logic for new/modified records such that

if Individual then
if len(lastname) = 0 or len(firstname) = 0 then
error -need to fill Lastname and firstname
....

[justgeig]

I think I understand the concept and I think that would work.

I just now thought of another idea to run past you. What if I were to have a table "Companies" and then a separate table "Contacts". Connect both of those in a one-to-many relationship to each of our publications (my company is a magazine publisher.) Then, either a company or a contact (individual not related to any business) would be able to place an ad into one of our publications. Then, I could have different fields for businesses that wouldn't apply to just an individual that places an ad.

That may be too fragmented....? Or maybe some other problem?

[orange]

What you are describing is a typical scenario where the actual business and business rules have NOT been defined. You aren't the first to go through this.
My advice, and you can ignore it, is to write a 1 or 2 line description/definition of what a Customer is? What a Contact is? What a Company is? etc. You will learn more about what your business is and how things relate than you can imagine.
The next thing will be how these things interrelate.

I'd be cautious of using Contact. That is typically what we use for the representative at a company. Basically, our Contact at a specific company. You can use Contact providing the definition and use of it are consistent in your business.

[RayMilhon]

You might also want to look at splitting it up. since you can have multiple customers at the same company have a company table and a customer table. Customer table would have a foreign key to the company table. Then in your query to get business customers use an Inner join to the company table. To get Individual customers query for company id = null.

[justgeig]

another good suggestion. Thanks to both of you!