DB Structure

[drkepyon]

Hi there, Im new to this access world, and I'm trying to create a DB to track rental incomes.

We have different locations, and some of the customers are established in many (if not all) of those locations. Price per sqft change from a location to another and may vary from one month to the next.

Here's what I've thought so far:

One table with my locations, Rented space, avaliable space, total space and general comments
One table with ID, Customer Name, Rented Space,
?????????????????????????????????????????????????? ???????????????????????????
?????????????????????????????????????????????????? ???????????????????????????
?????????????????????????????????????????????????? ???????????????????????????
Querys
Reports
Graphics

The thing is I cant find a Way to put together all the Monthly-Changing Price/Custumer information. 40 different locations with 10 to 20 different customers per locations...........



Any Examples on how to solve this?

Thanks

D.

[burrina]

See this link for examples: http://www.databaseanswers.org/data_models/

HTH

[ssanfu]

Also check out http://www.rogersaccesslibrary.com/forum/forum46.html
for tutorials on dB design.

I usually start with pencil & paper (or a big whiteboard) to start designing. It is easier to make changes in the design and see the relationships.
Spend the time on designing rather than just start creating tables.......

[drkepyon]

Thanks a lot! Gonna dig into it

[NTC]

I believe the most commonly missed design concept by novices is the transaction table. There is a table for customer (or student or tenant or...) and there is a table for product (or property or class or....) and these are both relatively static stand alone tables.... It is the transaction table that brings the 2 together: Customer X, Property 1, Start Date, End Date

Microsoft anticipates this design by offering the LookUp field type whereby the transaction table's Customer field would be a look up of the Customer table and the Property field is a look up of the Property table.....whenever I post this other developers always opine that using lookup field types should be avoided - but I use them all the time and find them quite easy to deal with - - and they are particularly helpful for novices....

[burrina]

I most definitely disagree. http://access.mvps.org/access/lookupfields.htm
To each his own!

[ssanfu]

Unless the dB is for personal use only, something like a CD/DVD collection/address book, I would also disagree. For a novice/intermediate programmer, look up fields and MVFs cause more problems than they solve. There are tons of questions on why they can't find a record. The look up field hides the value that should be used to search - they try to search on name (text) instead of the PK (Long). That is, if they understand relationships.

The only time I *might* see a use for look up fields is in datasheet view. Since I never use datasheet view, I don't use look up fields.

Another problem that I see with look up fields (and MVFs) is when the dB becomes so useful that the decision is made to convert to SQL Express/SQL Server/MySQL/etc. The use of look up fields/MVFs requires a re-write of the FE. Again, this would be mostly for business use.


My $0.03 ... (inflation)