Nagesh S K wrote:
Mr Nagesh.

I have already done something similar, which may be of use.

The very first step you must take is not to create tables, but to write
a specification document.

Of what the tables will be, and possibly diagrams showing how these
tables interrelate.

This forces you to make decisions about the data.

For example. Does every part have a single drawing associated with it?
If so it can be stored in the part table as a BLOB.

If it might have three or four drawings, you must create a separate
table of drawings, each one of which must have its own unique drawing
number, and a link to the part it is a drawing of, in another field.

If it is possible that a drawing might correspond to several parts, you
must take a third approach. Namely a parts table, a drawings table *and*
a table of links between them so that any part can be associated with an
unlimited number of drawings, and any drawing can apply to an unlimited
number of parts.

This is the way I went with multi level kitting, so that a 'kit' is a
table entry that links - say - a sub-assembly part number to a series of
quantities of other sub-assemblies and/or actual bottom level components,.

That's standard bill of materials stuff - my system allows an order for
a complete unit to actually reflect into a purchasing requirement for
raw materials and an estimate for shop hours need to complete it,.
though I never implemented a form or report to show the latter.

You need to write that specification to code from ultimately. Although
MySQL is very easy to add fields and tables to, altering the entire
structure of a database is almost a complete re-write, so these are
decisions you have to make early, and right.

That is, you must analyse the business you are modelling first, and
decide how to map it onto the SQL structure optimally.

Although this makes for more complex forms, that have to deal with and
update several tables to complete a single 'transaction' or real world
update, in the end it is simpler than having data duplicated everywhere
to cover the cases you didn't think of when you started with the simpler
approach.

:-)

If you want to discuss this by e-mail, and your email address is valid,
say so and I will.

The fine detail of designing a database for a production workshop is
rather outside the remit of this group.

On Feb 25, 6:30*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:
Actually I was not think that big. Even though that may be direction
we may take further down the road. But it looks like we may have to
plan for that eventuality in the beginning it self. I am working on a
ER diagram. I will post it once I am done with it.

My e-mail ID is valid and I would like to discus this further.

Thanks and Regards

SKN

On 26 Feb 2011 12:01:48 GMT, "Erick T. Barkhuis"
<erick.use-net (AT) ardane (DOT) c.o.m> wrote:



What will be structure of this TOOLS_USED. I know it will have one
column operation_ID. which will be a FK. what will be the data type of
Tool_ID as it need to have many tool stored for each operation.
Earlier i was thinking like tool1,tool2....,tool20. But waht data type
will hold these values. Is array supported as a data type for a field.

Regards

SKN

Nagesh S K:

Hi!
Your TOOLS_USED will be a relationship table, which means, it holds the
PKs of TOOLS and OPERATIONS as foreign keys. Perhaps, you'll also have
other data about this tool usage, such as "enabled/disabled", or
"number of tools needed" (which might come handy if you need multiple
tools of the same type for one operation), etc.

So, that table might look like
CREATE TABLE tools_used (
tu_ID int(7) unsigned NOT NULL AUTO_INCREMENT,
tools_ID int(7) unsigned NOT NULL,
operation_ID int(7) unsigned NOT NULL,
further_field ....,
PRIMARY KEY (tu_ID)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

The tu_ID is not necessary, but I usually add a PK, anyway.

Now, say that you need three tools for operation 358. You would insert
INSERT INTO tools_used VALUES
(NULL,11047778,358,...),(NULL,10778988,358,...),(N ULL,10445566,358,...);
In other words: you create a row for each combination of a tool and an
operation. No arrays required. This way, you may combine any number of
operations with any number of tools, and you're flexible.

To find all tools require for operation-358, you would go like:

SELECT tool_name FROM tools_used AS tu
INNER JOIN tools AS t ON t.tools_ID = tu.tools_ID
WHERE tu.operation_ID = 358

--
Erick

Erick T. Barkhuis:

....
This was just to keep things simple at first. If you want to use InnoDB
in order to manage constraints, the table definition requires a bit
more work.


--
Erick

On 28 Feb 2011 07:15:32 GMT, "Erick T. Barkhuis"
<erick.use-net (AT) ardane (DOT) c.o.m> wrote:


Thanks now it makes sense.

Regards

SKN

On Feb 28, 12:15*pm, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:
Hi

I have created the tables and populated it with some sample data. But
I have some problem with following query.

select parts.part_no, operation_no,tool_list.tool_part_no,description
from
parts,tool,operation,tool_list as tl
INNER JOIN tool_list ON operation.op_id=tl.op_id
where operation_no="OP20";

The error is "1054 Unknown column 'operation.op_id' in 'on clause'


What is wrong with the select statement.

Regards

SKN

sknagesh (AT) gmail (DOT) com:


You are trying to mix JOIN-syntaxes, here. Best would be not to use the
comma-separated form, but to stick with the JOIN...ON...-syntax

SELECT p.part_no, op.operation_no, tl.tool_part_no, t.description
FROM tool_list AS tl
INNER JOIN operation AS op ON op.op_id = tl_op_id
INNER JOIN parts AS p ON p.parts_id = op.part_id
INNER JOIN tools AS t ON t.tool_id = tl.tool_id
WHERE op.operation_no = "OP20"

Correct field names and table names where appropriate.


--
Erick

On Mar 1, 2:22*pm, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:
That worked. But did not return any rows. But changing INNER JOIN to
LEFT JOIN did return correct rows.

Regards

SKN

In article <d417bb23-ca24-4857-b407-4c731ad1e964 (AT) a8g2000pri (DOT) googlegroups.com>, "sknagesh (AT) gmail (DOT) com" <sknagesh (AT) gmail (DOT) com> wrote:
You named a column that doesn't exist.