Defining data types


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: Defining data types

  1. #1
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5

    Defining data types

    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.

  2. #2
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    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
    Bob Rouse
    Dimension Data

  3. #3
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5
    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    You could do the tables like this:

    Code:
    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:

    Code:
    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.
    Bob Rouse
    Dimension Data

  5. #5
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5
    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?

  6. #6
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    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:

    Code:
    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...
    Bob Rouse
    Dimension Data

  7. #7
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5
    Thanks a lot for that, Bob. I really appreciate the time that you've put into helping me with this.

    Regards,
    Denis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center