Defining data types

[denis_v]

Can you please tell me the basic approach I would use to allow the users of an application to define their own data types, that can be nested to one level.

Specifically, this application would allow users to determine all of the electrical devices needed to automate all of the mechanisms in a new factory.

The factory would contain many different types of mechanism, such as conveyors, pushers and elevators. Each mechanism could have many different types of electrical devices associated with it, such as controls (push buttons and indicators), sensors, and actuators. For example, a conveyor may have a motor, Start and Stop buttons, and sensors to detect cartons on the conveyor.

The proposed model for this system is as follows:

A Factory has many Mechanisms.
A Mechanism has a Name and a MechanismType.
A MechanismType has a Name and one or more Devices.
A Device has a Name and a DeviceType.
A DeviceType has a Name and one or more IO (Input or Output).
An IO has a Name, an IsOutput field (boolean), and a SignalType (Digital or Analog).

So the user of the application would first define all the necessary DeviceTypes, then use them to define all the required MechanismTypes, and finally create the required instances of those MechanismTypes. If the user subsequently changed a device or mechanism type (such as adding a new sensor to a conveyor), that change would propagate to all instances of that type.

I don't know how to allow users to define types, so any help would be appreciated.

[brouse]

This is a big project, and you really need to break down what you need help with. There is no need to define "types" (be careful using that word, since it carries a lot of connotations in programming); the users need to be able to add/modify database values in various database tables. I see your project needs:

1) Table definitions: You seem to have a pretty good grasp on this already. I would make sure that when you create the various tables, you add primary key and foreign key constraints. Also, triggers to assist with forcing changes/deletions, etc. to roll downhill.

2) DB interface: (eg: ADO) Depends on the DB system

3) User Interface: What programming language are you using? You might want to post relevent questions in the appropriate forums.

In short - you'll have a better chance of getting the help you need if you can ask more focused questions

[denis_v]

Thanks for your reply, Bob.

Sorry I wasn't more specific, but I'm primarily interested in the design of the database. So can you please elaborate on your comments on primary key and foreign key constraints, and using triggers to force changes to roll downhill.

[brouse]

You could do the tables like this:

Factory Table
-------------
FactoryID	Int 	Identity	Primary Key
FactoryName	varchar(32)
FactoryDesc	varchar(128)


Mechanism Table
---------------
MechanismID	Int	Identity	Primary Key
FactoryID	Int	Foreign Key into Factory.FactoryID
MechanismName	varchar(32)
MechanismDesc	varchar(128)
MechanismType	varchar(128)

Devices Table
-------------
DeviceID	Int	Identity	Primary Key
MechanismID	Int	Foreign Key into Mechanism.MechanismID
DeviceName	varchar(32)
DeviceDesc	varchar(128)
DeviceType	varchar(32)

IOInfo Table
------------
IOID		Int	Identity	Primary Key
DeviceID	Int	Foreign Key into Devices.DeviceID
IOName		varchar(32)
IODesc		varchar(128)
IsOutput	boolean (or tinyint -> set to 1 or 0)
SignalType	tinyint (0 = Digital, 1 = Analog, 2 = ...)

Triggers would be along the line of:

CREATE TRIGGER TRIG_Mechanism_DELETE
ON [dbo].[Mechanism] 
INSTEAD OF DELETE 
AS

Declare @MechanismID int
Select @MechanismID = MechanismID from DELETED

Delete from Devices where MechanismID = @MechanismID
Delete from Mechanism where MechanismID = @MechanismID

You could do the same thing for the lower level tables....

Hope this helps.

[denis_v]

Thanks for that, Bob.

But I'm afraid I don't understand how this table structure would allow a user to define templates (rather than "types") for Mechanisms and Devices, which could then be used to create multiple instances of the required object?

[brouse]

First the user (or the program) creates a mechanism, and gets the MechanismID. Then he creates a Device. For every new device record, the MechanismID field is set to the MechanismID that was generated previously. For each new device, a DeviceID is generated that is put into each IOInfo table as the records are created.

In other words, even though the system is designed from the bottom up, the records are created from the top down. If the mechanism gets changed, you can find every Devices record that contains the MechanismID, and every IOInfo record for every device record.

If you want to be able to reuse IOInfo records and/or Device records, you could put the links into separate tables, like this:

Devices Table
-------------
DeviceID	Int	Identity	Primary Key
MechanismID	Int	Foreign Key into Mechanism.MechanismID
DeviceName	varchar(32)
DeviceDesc	varchar(128)
DeviceType	varchar(32)

IOInfo Table
------------
IOID		Int	Identity	Primary Key
IOName		varchar(32)
IODesc		varchar(128)
IsOutput	boolean (or tinyint -> set to 1 or 0)
SignalType	tinyint (0 = Digital, 1 = Analog, 2 = ...)

DevIOLink Table
---------------
DeviceID	Int	Foreign Key into Devices.DeviceID
IOID	Int	Foreign Key into IOInfo.IOInfoID

You could do the same with the Device/Mechanism table. You might even want to add a table above "Mechanism" if the Mechanism is a "subsystem" that might get used in more than one larger project...

[denis_v]

Thanks a lot for that, Bob. I really appreciate the time that you've put into helping me with this.

Regards,
Denis