Refer to a record from its self

[HarryPotter79]

Hi,

Please forgive me for this, I might be doing this all wrong.

I need to create a database to allow us to keep track of groups of products.

E.G.

My company sells nuts and bolts, so I create a table
with a group for nuts and a group for bolts as thats how we buy them so they are pure classes.

However we do sell them as a mixed pack, nuts and bolts, so I have 3 selling classes, 'nuts', 'bolts' and 'nuts and bolts' with the last one being a adhoc class, it is made up of more than one pure class.

Because I can sell any of the three products I want to create the three groups and know what they are made up of, the ID will be hidden so only I know what it is, all other systems will lookup the 'desc' to get the group name.

I will later want to know all groups that contain nuts, which should return two groups, how do I get started, I would like all the data to be entered into one form, so people can add delete and maintain these selling classes with ease.
My problem stems from the need for a pure class to contain its self and an adhoc class to have more than one pure class entry.

So far no data exists, we have an outdated product file that need regrouping from scratch and this is going be my starting point.


ID Desc Type Pure class
1 Nuts Pure 1 ('Nuts')
2 Bolts Pure 2 ('Bolts')
3 Nuts and bolts Adhoc 1 and 2 ('Nuts' and 'bolts')


Please give me some ideas, this is all new so anyting goes, if you need more detail please shout.


Thanks Harry

[mshmyob]

Are you saying you ONLY sell 3 products or do you mean that you sell numerous products that fall into 3 categories of Nuts, Bolts, and Mixed?

After you clarify that I will give you some pointers.

cheers,

[HarryPotter79]

No I sell lots of products,

I am using these as an example, our product file is strutured to the way we buy products, ie we buy lots of different type os nuts and lots of types of bolts.

When we sell a mixed pack the product sale is recorded under the bolts product group because we dont have a logical group to sell them from.

To as the buying structure is set in stone I want to create a selling structure, so creating my own groups to keep some kind of selling logic.

As a matter of fact the buying group is hardware, which is too generic, I would like to create groups for nuts, bolts, nuts and bolts, nails and so on. Later I will attribute products to these groups.

As some groups contain a mixture of others I would like to record this.

Another way to look at this if I have one store that sell nails from one section screws from another these are two sections some stores may have a section that sells both combined, in order to keep track of all this I want to know who has a nails section or I want to know which stores have the combined section, at the moment this is imposable because all I know from any system is the all have a hardware section, only people with knowlage or adhoc spreadsheets know whats where but not what exact products are sold from where.

[mshmyob]

So you need to track and create purchase orders for products purchased from suppliers, you need to track sales to customers by store location, and need to track inventory.

Does this sound right?

cheers,

No I sell lots of products,

I am using these as an example, our product file is strutured to the way we buy products, ie we buy lots of different type os nuts and lots of types of bolts.

When we sell a mixed pack the product sale is recorded under the bolts product group because we dont have a logical group to sell them from.

To as the buying structure is set in stone I want to create a selling structure, so creating my own groups to keep some kind of selling logic.

As a matter of fact the buying group is hardware, which is too generic, I would like to create groups for nuts, bolts, nuts and bolts, nails and so on. Later I will attribute products to these groups.

As some groups contain a mixture of others I would like to record this.

Another way to look at this if I have one store that sell nails from one section screws from another these are two sections some stores may have a section that sells both combined, in order to keep track of all this I want to know who has a nails section or I want to know which stores have the combined section, at the moment this is imposable because all I know from any system is the all have a hardware section, only people with knowlage or adhoc spreadsheets know whats where but not what exact products are sold from where.

[MMcCarthy]

Structurally if you are looking at products the best way to structure it would be

Products (table name)
ProductID
ProductName (e.g. Nuts, Bolts, etc.)

If you then require a further clarification on products you would have a product details table.

ProductDetail
ProdDetailID
ProductName
Dimensions
Etc.

If you are looking at ordering then you are looking at an order table structure. The tables would be orders and orderDetails.

Orders
OrderID
OrderDate
CustID

OrderDetails
OrderDetailID
OrderID
ProductID
ProductDetailID
Qty

This is just a generalised structure to give you some idea. For further information have a look at our tutorial on Database Normalisation and Table Structures .

Mary

[HarryPotter79]

That sounds good, the first step for me is to create these groups, I am thinking that inorder to work out what pure groups make the combined groups every group has to have the pure groups assigned to it.

Then by default every pure group is assinged to its self

so group a is pure and containes only group a
group b is pure and containes only group b
group c is combined and containes groups a and b

I can then assign some store locations to these groups and query all stores that sell group a products.

I could do this by using two tables, pure and combined and entering them seperatly however to make this simple I only want the users to have one form to type in group name, type (pure or combined) and the pure group or groups that make up that group.

like I said If I am looking at this all backwards please tell me.

I can build a database to a spec, but I don't want to start off making this overly complicated and make it messy.

Thanks Harry

[mshmyob]

Now that we have a better understanding of what you really want to do I would suggest you first read the link Msquared indicated in her post. That link helps to explain the basics of starting a database design.

You should have a table for each 'subject' or 'noun'. For example if you want to track products you need a table for products, if you want to track store sales you should have a table for store.

Quickly looking at your requirements you probably want tables for

Product
Vendor
Store
Category (Nut, Bolt, Mixed)
Order (this would be 2 tables - Orders and OrdersDetail)

The category table could be eliminated if you want and add a field in your product table that has a combo box assigned to it in the form that has 3 preset values of Nuts, Bolts, and Mixed. depending on your relationships you may need some bridge tables.

Keep in mind this would be a minimum in my opinion.

As for for keeping it simple for the end user and having only one form, that is no problem. Keep in mind that the underlying database structure has nothing to do with how many forms you have. You can make the user interface any way you like - 1 form or a few forms it is up to you.

The key point is to do the database structure correct the first time.

Read the article and then post your structure that you come up with and then we can direct you if there are any mistakes. You ned to try it first yourself, if you want to learn.

cheers,

That sounds good, the first step for me is to create these groups, I am thinking that inorder to work out what pure groups make the combined groups every group has to have the pure groups assigned to it.

Then by default every pure group is assinged to its self

so group a is pure and containes only group a
group b is pure and containes only group b
group c is combined and containes groups a and b

I can then assign some store locations to these groups and query all stores that sell group a products.

I could do this by using two tables, pure and combined and entering them seperatly however to make this simple I only want the users to have one form to type in group name, type (pure or combined) and the pure group or groups that make up that group.

like I said If I am looking at this all backwards please tell me.

I can build a database to a spec, but I don't want to start off making this overly complicated and make it messy.

Thanks Harry

[HarryPotter79]

Thanks,

I did read the msquared artical, and found it very helpful.

I will knok something up and let you know how I get on.

The category table will be the way to go as a product can be in more than one category.

Thanls for your help.

[mshmyob]

If hat is the cast would be a Many to Many relationship therefore put a bridge table between the 2.

cheers,

Thanks,

I did read the msquared artical, and found it very helpful.

I will knok something up and let you know how I get on.

The category table will be the way to go as a product can be in more than one category.

Thanls for your help.