Table structure Have I blundreed

[floorboy]

Hi fellow Accessers. I'm creating a database for a carpet firm I work for. Please excuse my naivety, I'm 54 and self taught. We have thousands of samples and thousands of products.

I've got to the stage where I have Produced a navigation form which kind of works so that I can input new product details. In order to achieve it I have made 8 separate forms from 8 separate tables which reflect different product attributes and put them on the left navigation.

I'm wondering if I've made a blunder of terryfing preportions by breaking down the product tables over 8 groups. I can't help think that I should have all products on one table and maybe use query's to pull groups of attributes onto the forms,

I know that what I do now will have a profound effect later on in development.

If someone can steer me in the right direction, I'd truly appreciate it., regards, floorboy

 

[stopher]

I does sound like you should use a single table but I wouldn't want to assume that without understanding your data better.

As an example though, consider a table representing houses. So for a given house you might record basic info about that house e.g. address, house type, number of rooms etc. this could be a very long list but it should still be in the same table.

However, suppose you want to record a next level of detail such as the dimensions of each room. This should go in a separate table "Rooms" with the foreign key relating to the house table. By doing this you can record the details of multiple rooms for a single house.

hth
Chris

 

[Cronk]

FloorBoy

Your feeling is correct. Carpet types should be in the one table. There will be massive, maybe insurmountable problems having separate tables.

Post your intended structure of the tables, including only main fields.

A long time ago I remember hearing a presenter say the first thing to do in designing a database system was to turn your computer off, meaning get your design right first before launching into building it.

 

[floorboy]

Thankyou Stopher & Cronk.

Ok here goes, my tables look like this

tblSuppliers tblProducts tblProduct_Sales tblSales
PK Supplier_ID PK Product_ID FK Product_ID PK Sales_ID
Supplier_Name FK Supplier_ID FK Sales_ID FK Product_ID

tblSamples tbleSamplesProducts
PK Sample_ID FK Sample_ID
FK Product_ID

I have yet to do the many to many SuppliersProducts joining tbl and I've done tables for Purchases, Employees, and Customers.

I pretty much have the rest of the relationships done and think I'm going in the right direction. I've been able to replicate a nice navigation form diplaying various product groups over several forms.

The next step is to display on those forms the many suppliers of that p[roduct, along with several prices. These can be up to 10 suppliers each offering several prices like a cut length, a roll price and various other prices 3 roll, 5 roll 10 roll etc....

No doubt I'll get stuck again, but I keep plugging away........Mike

I also have all the usual tables, Employees, Purchases, Customers and I've done those relatioinships.