× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



We've got an application that allows our users (admins) to define what
we call UDF (Used Defined Fields) in our UED (User Extensible Database)
:) Our DB layout, while simplistic, does actually work quite well.

There are actually five tables involved in our case:

1) Entity definition, IOW, what entity types support UDFs (Customer,
Vendor, Order, etc.)
2) UDF Types (Date, String, Number, Email, Picklist, Yes/No, etc.)
3) UDF Definition (Label, type, editing, required, etc.)
4) If Picklist, the list of valid value
5) The actual storage, that is the table that stores the actual value
for field-1 on customer-7.

In the case of the field definitions, we store:
ID (PK)
Entity_ID (FK to table 1),
SubEntityGUID (used in some special cases, eg: NY Customers have these
fields, but CA customers have those fields)
Name (Um, duh :) The "label")
Optional (Can it be left blank)
DefaultValue (Especially good for pick lists)
Type_ID (FK to table 2)
OnMainScreen (should it show on the main edit screen)
OnSubScreen (should it show on a "sub" screen -- your tab concept)
Validation (A RegEx used to validate the data)
InvalidMessage (What to tell the user when it's wrong)

On the Picklist we actually just store the list entries that are valid.
We do ask the user at definition time if the entries should be shown to
the user alphabetically or "as entered".

On the storage table we store:
OwningRowGUID (See below)
UEDField_ID (FK to table above)
FieldValue varchar(1900) -- any value is stored in here.

In our case all our tables have a column on them called RowGUID, which
is a unique identifier. We then use this guid as a FK of sorts on other
tables when we need to refer to a specific row. If your key is an int or
something like that then you'll need to differentiate between Customer
2784 and Vendor 2784.

I can walk you though it sometime if you'd like, contact me offlist.

-Walden

PS. In the interest of full-disclosure, the system is SQLServer-based
not System-i, but the table design would hold in either case.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.