Designing a database

[jhahes]

Could someone please give me some help with designing a database in access.
I know excel really well, and took a class on access, and have tried a few times to get started with access but always to no avail.

I have a company that installs real estate signs

1. we have about 200-300 agents (which represent about 10-15 offices)
2. 3 things can happen to 1 sign -
-1. installed the first time (charge)
-2. have a sold sign, for sale sign, or flyer box put on(charge)
-3. Finally removed(free, no charge)
-4. the date would have to be tracked on all of these occurences
3. We have about 10 products or things that can be put or hung on a sign.


I can set up the offices and the agents, and link them just fine. I can set up the products and do the work detail. The problem I have and cannot figure out is trying to bill or invoice this. Some agents are billed individually and some agents don't get billed, there office gets billed as a whole. Could someone please offer some insight on how maybe to go about this from the beginning.

Thanks for any help
Josh

 

[ThreeCrow]

This web site may help, there are many free database models, some dealing in Real Estate. It should give you some ideas.



http://www.databaseanswers.org/data_models/index.htm

 

[GPGeorge]

You need to start by identifying all of the ENTITIES, or things about which you want to store data.

I see several here in your description:

REAL ESTATE OFFICE: an organization which engages in the business of real estate (I'm not going to attempt a more precise definition, but you need to do that at some point)

REAL ESTATE AGENT: an individual who engages in the business of real estate (again, you need to pin down the definition of an AGENT).

REAL ESTATE SIGN POST: a sign post involved in the business of real estate.

REAL ESTATE SIGN ACCESSORY: products or things that can be put or hung on a REAL ESTATE SALE SIGN POST

REAL ESTATE SIGN POST INSTALLATION: installation of a REAL ESTATE SALES SIGN POST at a client location.

REAL ESTATE SIGN ACCESSORY INSTALLATION: installation of a REAL ESTATE SALES SIGN ACCESSORY on an installed REAL ESTATE SIGN POST.

REAL ESTATE SIGN INSTALLATION INVOICE: an invoice generated for the cost of installing a REAL ESTATE SIGN POST or ACCESSORY.

Each of these ENTITIES becomes a table in your database.

Next, for each of these entities, you also need to identify ATTRIBUTES, or facts about the entity you want to track. For example, if you have an identifying number affixed to each SIGN POST, that ID will be an ATTRIBUTE of SIGN POSTs in the SIGN POST table.

For a REAL ESTATE SIGN POST INSTALLATION, attributes include the date of the installation, the SIGN POST installed, the location of the installation, and the agent for whom it was installed. You may need to track other attributes as well.

Inventory is going to be one of the more interesting aspects of this database.
You may or may not have some way of identifying each SIGN POST in your inventory. If you don't identify them individually, you should somehow be able to account for all of your SIGN POSTs by creating an INVENTORY table stating how many of each type of item you own. All installations, tracked in the REAL ESTATE SIGN POST INSTALLATION table, must either record the ID number of the sign used, if you track them individually, or the quantity of signs installed, so you can calculate the number of SIGN POSTS out in the field in installations and validate how many are left in stock before issuing an order to install a new one.

For each invoice to be sent to the proper entity, you need to record, as an attribute of the REAL ESTATE OFFICE Table, Billing Type: Individual or Office.
You'll also need to record billing addresses for offices and agents.

Once you get your ENTITIES sorted out, the next step will be to identify the relationships between them. For example, a REAL ESTATE OFFICE employes one or more agents. That's called a One-To-Many Relationship and there are specific rules for setting it up in the tables.

A REAL ESTATE SIGN POST can be used in one or more installations. Again, a one-to-many relationship.

That's enough to get started for now, although there is a good deal more to consider.

George

 

[The_Doc_Man]

George beat me to it. I'll answer a different way so you'll get another viewpoint on it.

Remember that for a business-oriented database, you are usually building a "model" of your business. Therefore, any entity or event in your business must be represented in your database, either by a table or a query or form that performs a specific type of update to a table.

Remember this very simple principle: If you want to ask your database a question, you usually have to have already told it the answer. Therefore, if you want to know when a post was removed, you have to record the event. If you want to know when a sign was changed, you have to record the event. If you want to know when an agent showed a property, you have to record the event. (You can see where this is going.)

Another thing to remember is that your business sometimes dwells on physical things like signs or accessories, but sometimes you deal in EVENTS - which are just as real as the signs and accessories. Probably, though, an event needs a time-tag whereas a sign or accessory or agent might not. In fact, signs and accessories - being physical things - might even fit in the same table, as two coded items amoung many.

It is up to YOU to decide what is important. Here is something I suggest now and then. If you have these items in your office already, so much the better. Get a big box of sticky note pads, some pens, a dry-erase board, and some markers.

On the board, write the names of your business entities. Agents, properties, sign parts (the posts, placards, etc), events, etc.

Now populate the "tables" by writing some samples on sticky notes under each entry. Fiddle with it to determine what actually has to happen to store data, gather data, etc. On ordinary ruled or blank paper, write up a couple of sample reports and draw up a couple of forms that you think you need. Now figure out how to get from the drawing on the board to the pictures you have on paper. You will, in the process of so doing, identify fields that must be already stored in the tables to support the forms and reports you want to see.

Consider this as an iterative process. You won't get it right the first time. Nobody ever does. (Sort of like Neo and the Jump Program in "The Matrix"...) And you will fall equally far. But by doing it on the dry-board first, you will have as soft a landing as Neo did. I.e. nobody gets hurt and you learn something in the process.

Here is another thought for you. Events are different in certain ways than static items. They have time tags, to be sure... but they also generally involve an intersection of two other elements of your business. (I am leading up to the topic of Junction Tables - which you can find by searching the forum for the topic name.)

An agent obtains a property from a customer. That's an event for agents and properties and customers.
An agent brings a customer to a property. That's an event for agents, customers, and properties.
An agent puts up a sign on a property. That's an event for agents, properties, and sign parts. (Ditto, "takes down" a sign or "changes placards on" a sign.)
An agent sells a property to a customer. That's another agent, customer, property event.

In this little analysis, you can see agents, customers, properties, and sign elements as "static" elements and a whole bunch of events involving three of the four elements as a single action.

I'll leave this here and let you mull over this for a while. It is DEFINITELY something for which you should apply liberal amounts of initial design work if you want it to be done right. DON'T HESITATE to return to the forum if you need more help, but may I suggest that you read up on the topic of normalization? Goggle-search for "Database Normalization" and look for a reputable university web site as your source. Many really good sites exist with thorough explanations of each level of normalization. For an Access DB to be really effective, you probably should aim for 3rd-normal form. (The articles on normalization will explain that for you.)