Data Modeling Problem
Posted in 1995
Topics: Performance & Tuning, SQL Development & Query Writing, Stored Procedures & SPL, Data Types & Schema Design, Triggers, Constraints & Referential Integrity
Hello: We're developing a client-server application for the specification and procurement of tagged engineering devices (relays, pressure transmitters etc.), using Powerbuilder Enterprise and Informix OnLine 5.01. We're in an early development stage. Each device object has a tag. Most tags are about char(12) in length, but can be upto char(26), which is the size of this field. The schema itself is somewhat hierarchical, with a device table (with attributes common to all types of devices) at the "root", and tables specific to each device type as its children. Device objects have many-many relationships with other supporting objects like documents. We have used referential integrity extensively, and the schema is essentially in 3NF. Presently, the primary key of the root device table (and the primary/foreign keys of all the child device-type-specific tables) is the char(26) tag. The primary key of the document table is the document title, again a char(20 something) field. In your experience, how much difference does it make in terms of performance, if the data types of fields used in joins are characters, as opposed to numbers? If we added serial/integer fields to be the primary/foreign keys respectively in this application, would that be worth a 4 day effort to implement the change? As for the possibility of propagating primary key (tag) changes, we could easily write an update trigger/stored procedure to do it. We would like to discourage our users to change tags, but real-world experience tells us that it does happen on occasion. We realize that integer comparisons are faster than character ones, but have no quantitative measure for the difference. All in all, what are the pros and cons in selecting a piece of data (character or otherwise) as the primary/foreign key, as opposed to a system-assigned number? Your insights would be much appreciated. Thanks a lot.