Database Design for a Professional Fisherman

[RCopeland]

Hello, I am designing a Database for my best friend who is a professional fisherman. While he is a very talented fisherman, he is extremely unorganized and unable to maximize his fishing assets. In my estimation he needs a program that will aid him in keeping up with his equipment and help him plan for future tournaments. This is the design that I have currently created. My question is: is this an efficient design?

 

[jdraw]

Would he be keeping records of the fish he catches. Things like

species, size, weight, weather conditions, location, water temperature.....
I didn't see anything in the current design for such info.

Just a thought.

 

[Pat Hartman]

The relationships are all wrong. Your relationships are data field to data field. Relationships are primary key to data field / primary key / partial primary key.

The primary keys should be named after their tables. It will save lots of confusion later. Then the foreign key should have the same name. so for example, the PK of tblSupplier should be SupplierID and instead of Maker as the FK name, it would be better to use SupplierID. That way when you look at a table, you can easily identify the FK fields. Some people prefix or suffix the PK and FK names with PK or FK. I don't because I don't use the ID suffix except for PKs and FKs and you can always tell by context which the field is.

It is also best to avoid special characters and embedded spaces in column and table names. Use CamelCase or the_underscore to separate words and make the names easier to read. Names should contain only letters (upper or lower case), numbers, and the underscore. It is also important to avoid using property and function names. I don't see any in your schema but words like Month, Date, Name, Year are already spoken for and can result in subtle and not so subtle errors if you use them as column name.s

 

[RCopeland]

To jdraw: No, he wouldn't be keeping up with each fish that he caught. Only weather or not he won or lost the tournament. The idea is to track how much money he has paid to enter tournaments vs how much he has been able to win back over time. This is just a little bit extra though. The real reason behind the program is simply to keep inventory over his fishing assets. Bait, Rods, Reels, and Entry Fee money.

To Pat Hartman: Thank you for your explanation of my relationship faults. I am going to work through them and try my best to modify my tables and relationships to be more correct. Although I am not exactly sure how exactly to edit my relationships to consist of only PK and FK relationships without completely restructuring my tables. Would you suggest that I need more or less tables than I already have?

 

[jdraw]

So, the application is more "Inventory" than "Fishing".
There are free existing data models, that may be helpful at
databaseanswers.org

This one deals with IT inventory, but I think you may see some similarities to your needs
http://www.databaseanswers.org/data_models/inventory_of_it_assets/index.htm

 

[RCopeland]

Alright, so here is my current dilemma: I need a table for all inventory items and their quantity. The problem is creating individual tables for each type of inventory item (i.e. rods, reels, spinnerbaits, worms, etc.). I need to be able to make entries into specific item tables because each inventory item has different description qualities to be entered (for example: rods would have length, action, and taper for their columns while reels would include gear ratio, and line capacity. But I want all entries from all inventory tables to show an entry into a complete inventory table with simply the item name and their quantity. Is this possible?

 

[CraigDolphin]

In that case you need to abstract your data model some more.

You have various assets to track of ceretain types (rods, reels, lures, etc). Each type of asset has different attributes (taper, length, etc) you want to also track.

To me that'd suggest a structure like:

Table: AssetTypes
Fields:
AssetTypeID (pk, auto)
AssetType (text)

Table: Assets
Fields:
AssetID (pk, auto)
AssetName (text)
AssetTypeID (fk)
AssetQuantity (integer)

Table: Attributes
Fields:
AttributeID (pk, auto)
AttributeName (text)

Table: AssetAttributes
Fields:
AssetAttributeID (pk, auto)
AssetID (integer fk)
AttributeID (integer fk)
AttributeValue (double)

Now, this always assumes that there's a numerical value associated with each attribute for a given asset. If you might end up with alphanumeric data instead, you could handle that by changing the data type to text for the AttributeValue field, or by storing unique answers in a table of answers, and storing the fk for the relevant answer in the AssesAttributes table.

Note that you could also add a AssetTypeID (fk) field to the Attributes table that would be useful in limiting particular attributes to particular types of asset (eg cascading combos on a form).

This structure allows you to add as many, or as few, attribute details as you want for each asset that you wish to track.

 

[RCopeland]

CraigDolphin, this is exactly what I needed! Thanks a ton!

 

[RCopeland]

Alright, I set up my tables this way, but now I keep getting "The expression is typed incorrectly" notification.

 

[CraigDolphin]

In what context are you getting that error message?

 

[RCopeland]

Let me try rephrasing my question in a different way. I will just describe the form that I envision being able to use:

Upon the form opening, you have the ability to enter the name of the new asset and a combo box to select the type of asset that it is. Then you click a "Next" command button that moves you to a new form. In this new form, based upon the selection in the combo box for type of asset, you will see a generated group of new fields to enter. For example, if I select Rod in the combo box, after hitting "Next" I will be able to add length, action, and taper. If I were to have selected Reel in the combo box, I would then be able to add gear ratio, and line capacity.