Newbie build issue

[Lincoln Nail]

Newbie here with a build issue. I am attempting to build my first true related DB, and think I am overanalyzing something easy.

I run a rent-to-own store, where we have customers (sometimes joint apps) fill out a form with their info, and then someone verifies their info. Things that we verify are their address through the landlord, their employment through the employer, and their references. I am wanting to move this verification process to a DB, so that we can move to a paperless storage of the information given, as well as a standardized verification procedure.

The problem that I am having is what exact tables I should create, what keys, and the proper relationship. Just as soon as I think I have it figured out, I overanalyze a future situation and get lost again. And then there is the addresses. Should it be tabled in Customers or Landlords?

Right now, I have laid out on paper: Five tables (Customers, References, Employers, Landlords, Notes).

Customers have many references, and some references may be for multiple customers.

Customers have only one employer (unless it is a joint app, then there may be two employers), but I would like to track all of my customers that work for a certain employer.?.

Customers will have only one address at a time, but I would like to be able to track all of the customers that rent from a specific Landlord.

EDIT Since addresses are an attribute of both Customers and Landlords, which table is recommended to put the address fields into?

I would also like to create multiple Notes for each customer, reference, employer, and landlord.

Customers has a Parent Key

References has a Parent Key, and a Customer foreign key

Employers has a Parent Key, and a Customer foreign key

Landlords has a Parent Key, and a Customer foreign key

Notes has a Parent Key, a Customer foreign key, a Reference foreign key, an Employers foreign key, and a Landlords foreign key.

I have drawn out the Customers Parent key to:

References TO Cust_ForeignKey

Employers TO Cust_ForeignKey

Landlords TO Cust_ForeignKey

Notes TO Cust_ForeignKey

and mapped out the Notes Parent Key to:

Customers TO Notes_ForeignKey

References TO Notes_ForeignKey

Employers TO Notes_ForeignKey

Landlords TO Notes_ForeignKey

Is this all of the relationships that I need to create? I am getting lost in the possibility of multiple references for multiple customers. Also, I am in odds about what to do about the "Secondary" customer. There is only one secondary customer per customer, but that secondary will have their own employment, and how would this track properly for an Employer's employees (customers). I am also not quite sure if I have a proper Notes setup.

I am really needing opinions and guidance, before I build the solution. I am a quick learner (always have been), but I get stuck at some of the easiest stuff and need a slap in the face for me to see the problem. Am I going the right direction for my needs, or am I way off base? Am I missing fields, relationships? Am I not thinking about something that I should? Also, when tracking SSN's, where only one SSN (Social) can have a record, is it better to use this as the Primary Customer Key?

I appreciate you taking the time to read and respond!!

Lincoln

Edited February 9, 2009 by Guest
ADDED TOG

[Oldsneekers]

The first question is how do you want to use the data? I am guessing that you want to know who (customers) has what stuff (rented_stuff) and all the information about the customer (References, Employers, Landlords, Notes).

The next question is will there be more than one record for each related type of data? Obviously you have lots rented_stuff and if rented, can be linked to many customers, just not simultaneously. I also assume that both Employers and Landlords can have multiple customers of yours. I'm not sure about References, I guess that's customer specific. If a given reference is likely to be linked with only one customer, than that information can be part of the customer record (in appropriate fields) rather than a separate table altogether, (but if a reference can be linked to more than one customer, that can be treated similarly to Employers).

Tables and fields (some fields any way)...

RentedStuff: itemName, IDNumber, ItemDescriptor.

Customer: Names, IDNumber, Address, Phone, ReferenceName, ReferenceAddress..., EmployerFirstID, EmployerSecondID (assuming he works a second job to afford his lavish lifestyle , LandlordID,

Employers: EmpName, IDNumber, EmpAddress...

Landlords: LLName, IDNumber, LLAddress...

Transactions: RentedStuffID, CustomerID, Date

The tables RentedStuff and Customers have one record for each specific item.

Transactions is where the action is. It is the table that relates the Customers to RentedStuff. Thus you can relate and view data Customers to Transactions to RentedStuff and visa versa.

I hope this help.

OS

[Lincoln Nail]

OS

I don't plan on tracking inventory or transactions (per se). I only plan on tracking the verification process, it's results by store, customer, etc., and through it the customer information can be used for marketing purposes (cust, references, landlords).

Stores can have multiple customers, addresses, employers (businesses), notes, and references.

Customers can have a secondary applicant (which may have their own employer).

Customers can have many notes.

Customers can have multiple references.

References can be related to many customers (maybe relate by phone number?). Or possible "Similars" portal (as reference names may not be entered correctly).

References can have many notes.

Customers will only have one address, and by it one Landlord - although they could move after doing business with us the first time, come back again (new order form), and we need to change their address, and we would like to track all of their previous addresses too.

Addresses can have many notes.

Landlords can have multiple addresses.

Landlords can have many notes.

Customers (Prim & Sec together) can have multiple employers.

Employers can have many customers, and multiple locations (addresses).

This is pretty much what I have defined so far. I think I may have figured out how to put it together, and will post a pic of my TOG as soon as I get it put together. But, I am not sure if I am going the right direction with it. I'd like to build it right from the start.

Our process in-store is:

A customer fills out an order form, which has things like their name, address, SSN, employer name & number for each the Primary "Account holder" (cust) and the Secondary "Account holder" (if added to the order form), landlord name & number, and five references. Being rent-to-own, we don't check credit, we just verify the customers information with the references given (landlord, employer, references). Currently, we write the verification information on the back of the order form to show "completion". I am trying to add the customer and verification information to a database, for a few reasons. One, we would like to be able to track the overall performance of applicants vs. rentals (we can check rental status through our rental software). Two, we would like a database of addresses to market to, which includes customers, their references, businesses (employers), and landlords. Three, we would like to see the relation of rentals to specific addresses, landlords, employers, references, etc. (i.e. This reference knows six different rental customers, but doesn't rent himself ; We have a lot of customers that rent houses from this one landlord) Four, we would like to look at "Similars". Using the example of an exuberant amount of rental customers that rent houses from one landlord, references could live at addresses that old customers used to live in (so the address is already related to a landlord), we can see marketing prospects due to the address being similar, and hopefully the same.

Obviously, the relations are important, which is why I am looking for a little guidance. It stands to see if I am simply overanalyzing things and becoming overwhelmed. The first time is always the hardest.

I will post a pic of my TOG as soon as I get it done, but if you or anyone else has any guidance in the mean-time, please let me know.

L Nail

[Lincoln Nail]

OS

I added a pic of the TOG to my original post. Am I going the wrong direction in my relationships, considering what I am needing to do?

LN