Need a little help please

[chobo321321]

In theory the tables below, and their relationshio make sense to me. I know I could have done two tables, and skipped the third table, but for this project it has to be like that.

Right now I am using an autonumber dataype for tblPersons (PerID), and using an int for the other two primary keys on the other tables. But, what I realized pretty quick was if add a new record(in the datasheet view starting with tblPersons because it won't let me add new records until I have a PerID) I can only have one subdatasheet, but I need two. One for Regualr customers, and one for business customers when I add records to tblPersons. Also, the way it is setup now a regular customer can have a CustID of 1, and a Business Customer can have a BusID of 1 which is stupid and makes no sense since they are in a 1 to 1 relationship with PerID...

Sorry for the confusing questions, but this in my opinion is pretty basic, but almost impossible to find answers for. Any help is appreicated.

 

[Jings00]

Why not use only one table for all the data? Add a field for ContactType "B" or P" and then the subset data could be in dual use fields (CusBusID for example) - If some data does not pair up well with data from the other type you could and would have fields that have a use in one contact type, but not the other. - just a thought to keep the database set-up simple.

 

[chobo321321]

I'm not sure if I understand you correctly. Do you want me to put all the fields in one table, and just add a new field with contact type? If thats the case I would have a lot of Null fields. I'm kinda hoping someone could help me get the original example working.

 

[Jings00]

Yes, with the single table set-up, you would potentially have null fields for data that does not match the person type you are entering, but so will you unless you intend on doing all outer joins to the subtable data. I think you are making something simple overly complex by doing this and once you start adding additional tables and relationships, the set-up may become a burden for future development.
Either way, as for you form - I would not use a subform for data entry if the relationship is 1-1, I would put all the fields on the main form. - possibly hiding/disabling the ones that are not relevant.

 

[chobo321321]

I agree it does seem to be overly complicated, and if it was up to me I would have to two tables (tblRegualrCustomers, and tblBusinessCustomers), but my teacher has this obsession with having a third table for common fields I'll try it the way you proposed. Thanks for the help.

 

[Pat Hartman]

I disagree with how your tables are named. The "center" table which you are using to hold the common fields should be named tblCustomer and all three ID fields should be CustomerID. Changing the name of the id in each table simply obfuscates the relationships. The "right" table should be named tblPerson.

Since you can't have two different subdatasheets, you would need to use a subform or popup for to show the "specific" data.

 

[chobo321321]

Thank you so much for the help Pat, these three tables are driving me crazy. I made all the changes you suggested. For the data entry I'll try to make an option for selecting between business, and regular customers.

Is this the way people usually setup tables in the business world? I would have preferred having just two tables even though some field names are the same name.

 

[Pat Hartman]

If there were only a few different fields, I would use a single table with an indicator that made it easy to identify which records were for individuals and which were for companies. Otherwise, what you have is the correct structure. Two completely separate tables is not correct and would prevent you from enforcing RI with the rest of the tables. So, it is one table or three. In some systems I've worked with, it was more than three. When you have many different entities that can be a "party" to actions in the rest of the db, and those entities have numerous non-common fields, you end up with a "party" table and a number of related tables related 1-1 with "party" which in your case you are calling "customer".

 

[chobo321321]

Thanks for the explanation, Pat. I had no idea using two tables would break referential integrity. It's kinda sad that I have already taken a basic access course, and this sort of thing was never brought up or explained...

 

[Pat Hartman]

This is a rather complex problem for a basic Access course. Most basic courses would simply go with a single "party" table to handle both companies and individuals and not discuss any other option. this actually works fine as long as you can live with a few extra fields here and there. Or, if the course were taught by someone who didn't understand database design, they might actually suggest the two-table solution. When they got to the point where they needed to establish RI between the Orders table and the two types of customers they would discover a problem. The problem is that one foreign key can only be used to establish RI to one table. So, they would probably complain about how "inflexible" Access is (no other RDBMS supports this either) rather than finding the correct relational solution which is the "party" concept and the three tables you have now.

You wouldn't normally be given a problem like this unless you took a more advanced database design course.