Re: Newbie to RDBMS, Informix...E/R modeling question

Try building a table called LocationType. The key is LocationTypeId and the data is LocationTypeDescription. For example (add your own descriptions): 1 Home address 2 Business address 3 Vacation address 4 Temporary address Now build a table called CustomerLocation. The compound key of this 2nd normal form table is CustomerId and LocationTypeId. The data is StreetNumber, StreetName, etc. Customer---->CustomerLocation<----LocationType Bill MacLean <bmaclean@worldnet.att.net> wrote in article <32B437E2.3BD3@worldnet.att.net>... > David Carmean wrote: > > > > I'm trying to create a "contacts" database with SE V6.01 or so... > > I'm new to Relational database theory, and to Informix as well. > > I'm educating myself with the Informix manuals, and Date, C. J., > > _An Introduction to Database Systems_, 1996. > > > > The Informix Tutorial suggests a need to resolve many-to-many > > entity relationships with an intersection table. I'm having trouble > > determining how to populate/update the database if the original > > base relations no longer have foreign keys. > > > > For example, say I have the relations "name" and "address"; each name > > can have multiple addresses of different types (postal,physical,home,etc.), > > and each address can be associated with one or more names. > > [snip] > > > BUT...if I do as suggested in the tutorial, and move the foreign keys > > to a new base relation which is the intersection of the two original > > relations, what technique can I use to populate the "address" table > > then? How do I associate unordered records in the two original base > > relations "name" and "address" with each other, when there are no longer > > any foreign keys to use? I mean, how do I create/derive the > > intersection when one of the tables is still empty? > > > > I must be missing some concepts still. > > If I understand your question, you can't. You have to populate the "parent" tables before you populate the > intersection table. People must first populate the "Name" entity and "Address" entity, and then you can > associate the two via your intersection entity. > > > > C.J. Date apparently doesn't think much of the Entity/Relationship > > model. > > I wasn't aware of this, but I think there are some pretty good reasons for not being fond of ER modeling. > One of the things that can be difficult in ER can be deciding what is an entity and what is an attribute. > For instance, in your example, I wonder if you really need an address entity at all. Here is the table: > > > create table address > > ( > > addr_idx serial, > > orgname char(20), > > mailstop char(20), > > street char(20), > > city char(20), > > state char(2), > > postal_code char(10), > > country char(20), > > addr_type char(10), > > rec_num integer, > > primary key (addr_idx), > > foreign key (rec_num) references name (rec_num) > > );> > > > Set aside consideration of the "addr_type" attribute, and your surrogate key "addr_idx" for minute, and it > looks to me like all the other columns are actually part of a large composite key that identifies a > particular address. Now consider the "addr_type". I don't know about your business rules, but I doubt that > "addr_type" is actually functionally dependent on address. For instance, many people run their business from > their home, so the same address may have an addr-type of "Business" and addr_type of "Home". If this is the > case, Address doesn't actually play any functional roles, so it probably doesn't need to be an entity. If you > wanted to record the existence of gas storage tanks at an address, or the taxes leveied on an address, or > anything specifically about an address, then you need an entity. > > You also might consider making a "Person" entity instead of a "Name" entity. You perhaps need another entity > called "Organization", and then you can have the Orgnization represented as a foreign key in the Person > entity. This way, you don't have to repeat the name of the organization (with all the attendant risks of > mis-spelling, etc) as you would have to in the original table (below): > > > create table name > > ( > > rec_num serial, > > orgname char(20), > > lname char(20), > > fname char(20), > > middle char(20), > > handle char(10), > > name_type char(10), > > primary key (rec_num) > > );> > Creating a seperate Organization entity would also allow you to record specific facts about the organization > (e.g. phone number, organization type, etc). > > > I think these types of considerations are much easier to address through a conceptual model, rather than a > logical model (which is what most ER models are). > > The best conceptual modeling method that I know of is called Object Role Modeling (ORM). ORM is supported by > a tool called Infomodeler, from Conquer Data in Bellevue WA. > > An ORM conceptual model is not some vague or ill-defined "broad brush picture". On the contrary, it is very > detailed in that it contains every object and fact that the database must store, and every data (or most) > oriented business rule that the database must enforce. The thing that makes conceptual modeling easier is > that it allows you to work on understanding the business without rushing into table design. For instance, in > an ORM model if you have a many to many relationship, you represent it that way. Once you have the conceptual > model correct, you apply a mapping algorithim (which is automatic if you use a tool) to the model that > automatically creates a properly normalized logical model. At the conceptual level, you don't treat entities > and attributes differently. Everything is an object, and the entities and attributes "fall out" during the > mapping process. ORM and the tool that supports it (Infomodeler) are neutral toward ultimate database target, > but as an Informix user, you will be happy to know that Conquer Data has announced that they will fully > support modeling for Informix's new Universal Server and datablades. > > With ORM you focus on the business data, and the correct table structure automatically follows. > > The best book on ORM is "Conceptual Schema & Relational Database Design" 2nd edition. By Dr. Terry Halpin, > Prentice Hall Australia. ISBN 0-13-355702-2. You can get an overview of ORM by downloading the paper > ORMVIEW.DOC from ftp://ftp.asymetrix.com/pub/infomodeler/Other > This is an MS Word format paper that shows