Line Item Issue

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a LineItem table that needs to pull data from one of 3 tables;
sinventory, nsinventory, serviceitem - depending on the state of an ItemCode
field within the LineItem table. The structure required for each item code
type varies for any given option. nsinventory has a product field and a qty
field, sinventory has a product field and a serailnum field, serviceitem has
a servicetype field and a memo field. I'm stuggling with what is the best
approach to handling this. Currently my 'best' idea is to create a table
that contains all 6 fields of which only 2 will be of significance for each
record. This seems a bit cheesy and creates an issue when displaying data
that will need to be resolved programmatically.... I'm wondering if there is
a better way.
Any suggestings would be appreciated.

Thanks,
mike
 
Hi Mike,

You need to think hard about the semantics of the situation. In
relational database terms, a table that contains three different kinds
of record which have no fields in common is not a table at all, it's
unworkable nonsense.

In any case, you already have the data in the three tables, so you don't
need to duplicate it in a fourth table. Instead, use queries to
assemble/combine the data you need as and when you need it - although
unless the existing tables are related on some field you haven't told us
about, I'm not sure that that will be possible.
 
Mike said:
I have a LineItem table that needs to pull data from one of 3 tables;
sinventory, nsinventory, serviceitem - depending on the state of an
ItemCode field within the LineItem table. The structure required for each
item code type varies for any given option. nsinventory has a product
field and a qty field, sinventory has a product field and a serailnum
field, serviceitem has a servicetype field and a memo field.
Click to expand...

First of all, I'd combine the three tables into one table with three fields
like so

Product
TypeCode (S, N, or I )
DataField

The TypeCode would, of course, reflect the type of contents in the
DataField. If TypeCode=S, DataField holds the qty. If TypeField=N,
DataField holds the serialnum, and if TypeCode=I, DataField holds
a memo. The type of the field DataField is always Memo but it can hold
any kind of data required. Then your problem is greatly reduced.

Tom Lake
 
Tom,
I like this approach. This gives me something to think about. handling the
variable data in a memo field is an approach I hadn't thought of.

-mike
 
Thanks John. I'm looking into your suggestions. The table does link
records together by a ticketnum key.
-m
 
Tom,
Could you comment on this tweak to your approach.
Use 4 fields.

ItemCode (S, N, or I)
Product
Data
QTY
--------------------
If ItemCode=S
Product=3 Part Widget
Data = Serial Number
QTY = 1

If ItemCode = N
Product = Hex Bolt 3"
Data = Null
QTY = 37

If ItemCode = C
Product = Consulting
Data = Text here descripting the consulting service performed.
QTY = # of consulting hours
 
Back
Top