Best way to handle this Web Database

[tucker1003]

I could breeze through this database that I am building if it didn't have to be


a web based database. I am having a hard time finding answers and now questioning my design do to the difficulty of finding answers specific to web based databases.

I can answer all my questions by building my data table with over 50 fields, but I was hoping to split it up a little. To split it up a little The tables should be one to one relation.

One problem I am having is the default "ID" field in one of my subforms keeps causing the "Enter Parameter Value ID" dialog to pop up. I can't get ride of it.

In Web database you don't get design view of anything, and I am having a hard time navigating to properties of anything. Are there any books written for Access 2010 exclusively teaching about web databases? I noticed that
you don't get to view relationships, but you create them through a lookup field instead.

Currently my Data Entry form contains 5 tabs
tab 1 has 7 fields
tab 2 has 8 fields
tab 3 has 11 fields
tab 4 has 15 fields (currently a subform)
tab 5 has 10 fields

Should I make it into one big table?

[Dalagrath]

It all depends on your data to be honest. I could link you websites like this, but in honesty we need more information on your data before we can suggest a design for the database.

[tucker1003]

Here are the details of my web database.

I'm creating a Dog Food Database to be used on a web browser, so my employees have all the information at their hands on the ipad for the customers. This data base will include everything about the product and manufacturer that we commonly get questions about. The information will cover 2 types of pets Dog and Cat, and all forms of foods (Dry, Wet, Raw, Fresh, Freeze Dried, Dehydrated). Every food has a Guaranteed Analysis with 13 most important percentages + 2 calorie numbers vital to calculating a pet's intake.

So here is what I need.

Parent Company (vital to companies integrity, i.e. Procter & Gambel)
Manufacturer (name of the company on the bag, i.e. Iams)
Co Producer (the company hired to make the product)
Brand Line (Brand line, i.e. Iams Proactive Health)
Product Name (specific formula, i.e. Adult Chunks)
Picture (picture of the bag, for customer reference)
Website (for any additional information and reference)
Pet Type (dog or cat)
Food Type (Dry, Wet, Raw, Fresh, Freeze Dried, Dehydrated)
Product Sizes (I will use 4 fields, helps customer know available sizes)
Product Prices (4 fields to match sizes, now we can determine $ per lb)
Ingredients (memo style field holding the entire list of ingredients)
Natural? (Yes/No)
Grain Free? (Yes/No)
Single Protein? (Yes/No)
Single Carb? (Yes/No)


Guaranteed Analysis (every food has one, I originally have this setup as a 2nd table and I created a subform)
Protein
Fat
Fiber
Moisture
Omega 6
Omega 3
DHA plus EPA
Carbohydrates
Ash
Calcium
Phosphorus
Magnesium
Sodium
kcal/cup
kcal/kg


Thanks so much in advance for taking a look, remember web databases are created a little differently then regular ones, like I can't use most symbols in the data fields. I know I can't use the "?" for a few of my fields. I get no design view and no relationship view.

[tucker1003]

Okay I spent some time reading up on Database design, and have a better handle of things and am breaking it up so I don't have redundant info. This is just a start, please see the example and then my starting design.

EXAMPLE DATA

Procter & Gambel Pet Care
(owns several dog food manufacturers; Iams, Natura, Eukanuba)

Iams
(produces several brand lines; Proactive Health, Healthy Naturals, Premium Protection, Veterinary Diets)

Proactive Health
(holds 24 individual products)

Would I be on the right track with the following?????

ParentCompanyTable
-- * ID
-- Parent Company

ManufacturerTable
-- ID
-- ManufacturerName
-- Website

BrandTable
-- ID
-- BrandName

ProductTable
-- ID
-- ProductName
-- CoProducer
-- website
-- picture
-- pettype
-- foodtype

I also need an idea of relating these tables.

[Dalagrath]

Now just so I am clear, each manufacturer has only one company and each brand has one manufacturer? If so, read on.

ParentCompanyTable
~CompanyID(PK)
-Parent Company

ManufacturerTable
~ManufacturerID(PK)
-ManufacturerName
-Website

BrandTable
~BrandID(PK)
-BrandName

ProductTable
~ProductID(PK)
-ProductName
-CoProducer
-Website
-Picture
-Pettype
-Foodtype

GAnalysisTable
~GAnalysisID(PK)
~ProductID(FK)
Protein
Fat
etc etc...

Now the reasons I set it up this way is that you can now add this table:

ItemTable(Or w/e)
~ItemID(PK)
~CompanyID(FK)
~ManufacturerID(FK)
~BrandID(FK)
~ProductID(FK)

Now with this ItemTable being the "junction table" between all of these other tables, you can now run a query for all the information on which Product you want etc. Also, because the ProductID was the foreign key(FK) to the Analysis, it will also show up if you use a subform like you said.

As for relating them with the relationship viewer, you can do lookup wizards in the design view of the ItemTable table.

[tucker1003]

I'm sorry for the confusion. Each Parent Company may have SEVERAL Manufacturers, each Manufacturer may have SEVERAL Brand Lines, each Brand Line may have SEVERAL Products, and each Product ONLY has ONE Guaranteed Analysis.

For the relationships I CAN NOT use relationship viewer because its a web database, that's where I have the difficulty. Without the viewer I have to use lookup fields which are a little bit different then I am use too.

[Dalagrath]

Using the table layout I showed you, this will allow each of your items to have a Company, a Manufacturer, and Brand. (Including the Product)

This way you can have any Company/Manufacturer/Brand combo and still be able to get the data you require.

I currently am unable to work on a database in my current location, but when I am home, I can make an example on Access if you want.

[tucker1003]

Thanks for your input and time, I really appreciate it. My Cousin In-law came by and we walked through my plan and developed a database design. Doesn't look to far off from yours! I fixed his laptop in exchange. Go figure a programmer who doesn't care or know a lot about hardware.

Again I appreciate the time and help!

[Dalagrath]

Anytime, I am pretty new to the scene, but I know a few things that might've helped! Good luck!