Parts Database Theory.

[Homegrownandy]

Background:

I might just need a break and then it will come to me. But just in-case here we go:

I have nothing created for this yet I'm just drawing up some ERD's. This is for an engineering company doing electrical installations. We will be using the database to create quotes and this is just a starter to be honest. (keeping it as simple as possible for now.)
I'm going to create a parts database. We wont be monitoring stock at this stage so it should be simple.

Requirements:

*parts list
*part categories
*supplier list
*groups - would be a collection of parts. for whatever reason.



Most of this I have sorted. But I cant figure out how to categorise parts.

we will use two categories here as an example:

Cables - needs fields such as: diameter, cores, colour

Network - needs totally different fields.

(sorry if this is getting confusing)


problem:
I want every part to appear on the parts table..... but depending on the category it will need different information stored somewhere.

so we have a Cables table and a network table now. I need to link the unique part id with the unique cable ID (or network). So we need a junction table. I cant work out how to structure that junction table..


I'm very confused. If anyone understands and could help that would be great. If not, ill create an ERD on Monday and try to make a clearer example.

Thanks

[ranman256]

youd have tParts table
and a child of this tPartSpecs table
ad infinite specs to describe the part...

tPart tbl
-------
id, name, etc...
123, Cable

tPartSpec tbl
id, spec, descr
123, Length, 50 ft
123, color , black

[orange]

I'm just drawing up some ERD's.

based on what?
What are your business rules?

[Homegrownandy]

There are no business rules. They are yet to be defined. That's the whole point of this post, to help me understand what the best process would be.

Ranman, Thanks for your input also. Ill have to have a think and come back.

[orange]

Andy,

You have to have some concept or rule --no matter how nebulous -- to create an ERD.
From your own post you have Parts, Cables and Networks. When you try to relate a Cable to a Network, you have/or are creating a business rule (even if you don't recognize it as such) that says:
--- a Cable is part of 0,1 or more Networks (or whatever applies in your situation).

Think of this from a different perspective:
Suppose your company said, Andy we need this "project completed in 1 month". You can hire designers, developers, buy off the shelf or whatever you think is best - just get'er done.

What would you do? What would you tell a prospective designer, developer with respect to your needs?
If you went looking for an off-the-shelf product, what are you criteria?

Good luck with your project.

[Homegrownandy]

There's not a lot to it, let me try and explain better.

Lets keep using cable and network as an example.

Cable and network are both a category for parts. They do not need any direct relation to each other, but they must both exist as a part. The only focus of this database will be a list of parts. Then we will have suppliers who provide these parts and their latest price for the part. (this can be updated via email automatically based on date criteria.).

None of the above is a problem. The only problem I have is where certain parts have a different set of data depending on their category.

So a cable is a part, but every cable needs: core/colour/diameter/armoured <- mainly as a way to filter results.
Network equipment is a lot more basic. It would just be a description of the part.

Possible solution:

I'm thinking based on what ranman has said, to have all the "details" for every part and type of part to be listed on that part. Then use the category's as a filter on the forms.

EG. user clicks cable. it only shows results for category_ID 3 (cables).

Its not hard at all but I couldn't work it out at all last week, I was trying to separate parts into different tables. (I'm still not against the idea, I cant see it working though.).

The form that opens when the user clicks cable will only show the fields I want.

let me know if that sounds like a good idea.

[Homegrownandy]

So in materials (this is the parts list, it would be easier for the post if I named it as parts. Sorry!) what I have done is put C_ before any detail relating to cable. I can add N_ later for any network details I need.

I don't know what the other information is yet until I start using data, this is just the first step.

[orange]

Is this info of any help to your set up?

Good luck.

[Homegrownandy]

Hey orange, thanks for that. I'll have a read and yes it's useful to me so thanks!

I don't actually need to know any technical details for the project, ill be given a list of 'parts'. But since I work in the engineering industry knowing what I'm dealing with does help. I'm office based for an engineering company, so I help out wherever I'm needed and do the database when I have spare time.

I'm sure I'm being very confusing when I say cable is different from network (when as you pointed out and have linked me, there is crossover between the two). I'm fairly confident with the way its looking now and how I can split the products.

A lot of that will be decided as we go to be honest, if we encounter a problem where things are hard to find them we can split the products up more. For now,as long as I have an idea how I can split the products up later Ill focus on getting the data in and getting feedback.

Thanks again.

[orange]

No problem, glad the link was of some use.
I've seen others on forums dealing with cabling and networks and their main focus was on junctions/connections and terminations. It was much like a city sewer infrastructure --what pipes are where; what connects to what; where does that line/pipe end; what are the various branches.

I'm a little surprised with the scope you envisage. I would think that parts and cables etc, and suppliers would tie in to acquisition and/or inventory. Anyway you seem to have things sorted.

Good luck with your project.

[Homegrownandy]

That may be the next step in a few years time. I'm not making ambitious projects, just improving what I can where I can as it is needed. It would be too complex at the moment to implement inventory management. I'm just working on creating quotations and a list of parts as quickly as possible and at the same time. Also automated price requests. Things like that.

The company was pretty much paper based when I joined here, and its an established company. So there are a lot of things that will take a lot of work to improve. I'm just looking where I can save most time and then working on that. Where you mentioned sewer infrastructure, that's actually what we deal with. But we deal with the electrical engineering side for automation.

Quite ironic really when the office side of it wasn't automated at all.