Database Design

[shutout14cf]

I am new to Access and am working on a project where I need to periodically import a list of companies and their respective information (i.e. company name, revenue, net growth, location, etc.) from excel.



My boss wants me to import these lists and create fields he can check if the company is already a client of ours or a "trash" company that we aren't interested in.

The problem is that he wants to be able to update these lists every six months or so (as the information about each company changes), but doesn't want to have to re-check companies he has previously marked as "client" or "trash".

What is the best way to go about setting this kind of database up?
Thanks

[jzwp11]

Welcome to the forum!

What I would recommend is to set up an appropriate table structure first. Then import the data into another table and then migrate only the info you want from the imported table into your table structure. You can have your boss go through the data and flag what he wants and what he does not want. At no time should you ever change that flag when you do future import relative to the company. I would never delete any records.

In terms of revenue and net growth, I would recommend appending new data as new records rather than updating an existing record. This will allow you to accummulate a history of the company from which you can observe trends. Depending on the trend, your boss may re-evaluate his initial evaluation.

With that said, setting up the appropriate table structure is the most critical step of a successful relational database application. In order to set up the structure, you need to evaluate what information you will be capturing both now and looking ahead somewhat as well. Then you have to evaluate that information to determine how it is related.

For example, a company can report revenue and net growth many times over the years, so that describes a one(company)-to-many(financial values) relationship. Additionally, revenue and growth are just types of financial indicators one might capture, so we might capture a list of these indicators in a table (as records) which will allow us to expand if needed in the future. An appropriate structure might be as follows:

A table to hold the basic company info
tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName
-logTrash (your boss's evaluation flag field, I would recommend a yes/no field to keep it simple)

A table to hold the financial indicators we want to track
tblFinancialIndicators
-pkFinancialIndicatorID primary key, autonumber
-txtFinancialIndicator (the name of the indicator)

Companies can report their financial info quarterly, semi-annually or annually etc., so it would be nice to have a table that just holds these various reporting period names (so we do not have to type them repeatedly)

tblReportPeriod(you might start with 3 records: quarter, semi-annual, annual, you can always add more later)
-pkReportPeriodID primary key autonumber
-txtReportPeriod

Now we need a table that tracks the company and those financial indicators and their associated values & for what reporting period they represent

tblCompanyFinancials
-pkCoFinancialID primary key, autonumber
-fkCompanyID foreign key to tblCompany
-fkFinancialIndicatorID foreign key to tblFinancialIndicators
-fkReportPeriodID foreign key to tblReportPeriod
-dteCoFinancial (the ending date of the period for which the value represents)
-currFinancialValue (the actual value of the financial indicator for the period)

Those are just some of my initial thoughts...

[shutout14cf]

Thanks! This was very helpful!

[shutout14cf]

jzwp11,

I'm now having a little trouble setting up the relationships between these tables.
In your example what fields would relate to one another?
Should I be enforcing referential integrity?

Sorry if I'm asking obvious questions. I'm just getting started with Access and this is all new to me.

[jzwp11]

No problem with asking questions; that is why we are here!

To join the tables, you want to join the pk of the one table with the corresponding fk of the related table. For example, pkCompanyID with fkCompanyID. All of the fk fields should be long number fields to correlate with the pk's autonumber datatype.


I would recommend enforcing referential integrity & cascading updates and deletes.

[HiTechCoach]

I would recommend enforcing referential integrity & cascading updates and deletes.

I do agree Referential Integrity is a good thing. I normally always use it.

WOW. This is the first time I have seen anyone actually recommending the use of cascading updates and deletes.

Cascading Update:
In a well design database you should never need this. For example if you are using the auto number data type then you would not be changing the primary key value for the record so cascading update would never be needed.

I find is a well design database you will never need cascading updates.

Cascading Deletes:
<shuddering>
This can be very dangerous. I know people that has accidentally mass deleted large amounts of data using this "feature". In testing I have done it. If you are braze enough to use it then make sure you have safe guards to prevent a user form accidentally delete lots of data.
</shuddering>

I generally recommend not using Cascading Delete.

The Best Practice method for deletes is to not actually delete records. Instead you use a field as a delete flag. You filter out the deleted records in forms and report so that they appear as deleted. When it is time for database maintenance is when the data is actually removed and possible archived.

[jzwp11]

I stand corrected. I never heard that allowing cascading updates/deletes was bad. But then again, I control what my users are allowed to do with code, so I never have run into any problems.

For someone just starting out, then it would be best not to allow the updates/deletes per Boyd's post

[shutout14cf]

Once I've set up the appropriate table structure in my database, how do I go about importing the data into this structure?

[jzwp11]

You said that your current data resides in Excel spreadsheets. You can import those into Access as new tables. I don't have Access 2007 here at work, but in Access 2003, you went into Tables-->New-->Import (and then selected the Excel file). You would then migrate the data into the table structure you created using a series of append queries.

[shutout14cf]

Sorry, my question was more about the migrating process. I know how to import the spreadsheet.

[jzwp11]

You will need to use append queries. They can get kind of tricky depending on how the data is organized in the imported spreadsheet data. Without seeing the actual data, it is hard to give you specifics, but I would recommend starting with the basic company information. Create a regular SELECT query from the table that holds the imported data but only include those fields that would correspond to the fields in the company table in the structure you created. Then change the query type from a SELECT query to an append query. Access will prompt you for the destination table. You will also have to correlate the fields of the import table to the correctly structure company table.

As to the table on the many side of the relationship with the company table, you will have to again create a query based on your imported data table and probably join it to your properly structure company table (in order to populate the fkCompanyID in the table of that makes up the many side of the relationship).