Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

The SQL Guru Answers your Questions...

---

Today's question comes from Jing W.:

Hey, dear guru, Now I am developing a website through which we can collect data from users into our database(SQL server 7). The idea is the users fill out forms which specify their requests for the products(our company produce valves). Besides the general data, they should also provide the technical information depending on the valve type. There are 2 categories--standard valve and special valve, whose "tech infomation form" comprise different fields. In the database, I've created a table for the general data (table A), 2 tables (table B, C) for technical information (one for each category). In table A there is a field named "valve type" (bit type). Depending on which radio box the user chooses, the following form may be either the form for standard valve, or the other. The key fields for the 3 tables are all called filename. Every record in table A has a corresponding one either in table B (if the valve type is "standard"), or table C (the valve type is "special") But I don't know how to present these 3 tables in the database diagram. What kind of relationship do they have? not 1 to 1, or 1 to many(or vice visa), then what else? There may be some problems with the database design. thus I would like very much to hear your good suggestions. Thanks a lot for help. Jing W.

Jing,

What you're trying to model is often referred to as a "subtype" relationship. That is, an entity (in this case, a valve) has two or more (usually very) different types that you need to store information about. So you may need to store different information about water valves, hydraulic valves, gas valves, etc.

One way to model this (and this is usually what I see people do) is to put all possible attributes of the valves into one table (that usually ends up being very wide). This isn't the best solution. If you look at a row containing info on a water valve, you'll have the basic valve information, the water valve information, and lots of NULL columns for the attributes that only make sense for the other types of valves. That's a lot of wasted space.

You've modeled it correctly. That is, you have a main table (Valve) that contains information common to ALL types of valves. Then, you have children tables (in this case, two - probably StandardValve and SpecialValve) that contain information for their specific type of valve. The primary key of the two children tables is simply the primary key from the parent table - a 1:1 relationship. A 1:1 with a twist, though, because your business rules say that the valve will either have a corresponding record in StandardValve or SpecialValve. If you choose to rigidly enforce that business rule, you may want to write stored procedures to handle inserts, updates, and deletes of the records in these three tables so you've got that business logic wrapped up nicely.

Depending on which modeling notation (and tool) you use, you may be able to represent this special relationship on the diagram. For instance, IDEFX1 notation uses a symbol that looks something like this:

              P 
              |
              O
             ===
            | | |
            C C C

(that O is really a bigger circle). Otherwise, just represent the relationships as 1:1 and make a note on the diagram or in the data dictionary.

As an aside, you don't necessarily need the bit field to tell you the type of a valve. You could LEFT JOIN to the child tables to determine that. But I agree, being able to peek at the "ValveType" is nice!

Sean

---

Read Other SQL Guru Questions

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article