Re: Max number of Tables ?

In article <3k4r72$bnu_at_aplinfo.jhuapl.edu>, Stan Novinsky <stan_novinsky_at_jhuapl.edu> writes:
|> surman_at_oracle.com (Scott Urman) wrote:
|> >
 

|> >
|> > You don't want each message to be a separate table, you want each message to
|> > be a separate row in the same table.
|>
|> This sounds good, but can each message be a separate row if each
|> message is composed of variable fields:
|>
|> ex: Message 1 is made up of the following
|>
|> Field 1 is INTEGER
|> Field 2 is CHARACTERS
|> Field 3 is REAL
|> Field 4 is CHARCATERS
|>
|> Message 2 is made up of the following
|>
|> Field 1 is CHARACTERS
|> Field 2 is CHARACTERS
|> Field 3 is REAL
|> Field 4 is INTEGER
|> Field 5 is INTEGER(80)
|>
|>

Hm. Tricky. How about this then. One table MESSAGES, which would look like

FIELD_TYPE VARCHAR2(20),
FIELD_LENGTH NUMBER,
DATA_CHAR VARCHAR2(100),
DATA_NUMBER NUMBER,
MESSAGE_ID NUMBER,
FIELD_ID NUMBER The idea here is that each field would be a row in this table. FIELD_TYPE would contain 'CHARACTERS', 'REAL', 'INTEGER', or whatever. FIELD_LENGTH would contain the length of the field (probably relevant only for type=CHARACTER). DATA_CHAR would contain character data, and DATA_NUMBER would contain numeric data.

The combination of MESSAGE_ID and FIELD_ID is a primary key, and uniquely identifies a message field. To get message 1, say, you would query where MESSAGE_ID = 1, and then print each field according to FIELD_TYPE.

It makes the queries a bit more complicated, but you only need one table. You can also add fields to existing messages, or remove them, easily without having to do DDL operations. Received on Thu Mar 16 1995 - 20:58:28 CET