Problem: Some of my customers are businesses, some of them are just plain people. In other words, entities who have first and last names. Whereas businesses just have a business name. If I create two tables to model both Companies and Persons, I run into the problem of losing the ability to maintain a CUSTOMER_ID for both types of customers. Customer -> (Person) (Company) ID (PK) I don't know how to relate to individual tables ie Person and Company in a sequential manner back to the Customer table.
The business will have a contact person. Use same fields as personal accts. business name is an optional field. Also create account type field for Personal or Business to filter by, if you need All accounts go into the same table. It shouldn't be hell
Have a table called Customer with an ID(PK). Have 2 tables, Person and Company. Both Person and Company have a column called CUSTOMER_ID which is a foreign key(FK) back to the Customer table.
Is it really as simple as this? So, for example, if I have 3 invoices, 2 of them point to a person/ 1 points to a company, and then I get another customer who is a person, then my Customer will be like: 1. Person Table 2. Person Table 3. Customer Table 4. Person Table That would be great! Also, I guess I would need a Customer Name field in my customer table and string first and last name together or company name (as necessary), right?
Sounds like u need inheritance? There are typically 3 ways to do that on the database level. A little bit search coulld do.
Just have a single Customers table with a flag to indicate whether the entity is an individual or a company. If the flag is set to Company the appropriate fields get filled out / shown in the app and vice versa for Individual.
This is probably the best I've heard, because to be honest, trying to update several tables and still keep the primary key singular and matching is truly more trouble than it's worth. Mainly because the app I'm using is a flat database, and some of the information is null, etc.
the party data model is totally impractical for what I am trying to do. If I were entering the data one by one into the database, or if I had customer representatives taking phone calls, it might work. But again, the party-data model doesn't even solve the primary issue of my database, which is to represent a customer as either a company with a company name or with a person with with a proper first and last name. And this is because, no matter HOW you want to present it, a business name is NEVER a person. In my model I need person and company to be on the same hierarchical level. In the party-data model, the person is about the roles they play always.
I'm not sure I understand why the method of inserting the data is playing so heavily into your fundamental data model design. You don't have to implement the entire model if it is more than what you need, but you can borrow concepts from the Party model. For example, your design issue that you are stumbling on is taken care of by 3 main tables in the Party model: 1. Party 2. Person (subtype of Party) 3. Organization (subtype of Party). Person and Organization are on the same hierarchical level in the Party model (they are both subtypes of Party).
in my "party" there is just a name - pick: Company name or first name last name, there are no common attributes but name
It doesn't sound like what you are doing is that hard. In your data input program just do if data==company insert.... if data==person insert... Just have two different table one for companies and one for people and third one with all customer ids.
Honestly, all you need are three fields: first name, last name, and company name. Assign a unique ID for each record and set condition for either both personal names or a company name.
The third table for customers with just a customer id with foreign keys back from the Person and Company databases definitely seems like the way to go. When you need to insert something, you just have to ask the Person or Company table if the customer already exists in the appropriate table, and then if not, you can insert a new one into the customer table, and then follow that up with inserting into either the Person or Company table.
