Stroing Custom Fields in database

[nvishnu]

Let me rephrase my question from a previous thread. I have a customer table
and contact table with one to many relationship. Issue here is that the
number of fields in contact is dynamic based on the customer. Any thoughts
about how to design? Anyone knows how the ERP systems like Oracle,
Peoplesoft or SAP achieve this?

 

[Jeff Boyce]

It sounds like you are describing a classic one-to-many relationship, in
which the "many" side could have zero, one, or many rows. This matches my
definition of "dynamic".

Is there a reason why you wouldn't use something like:

tblCustomer
CustomerID
CustomerName (or ?FirstName, ...)
...

tblContact
ContactID
CustomerID (fk from tblCustomer
ContactFirstName
ContactLastName
...

You didn't describe the details so I can't tell if both Customers and
Contacts are people, or only Contacts. Is there a chance that you could use
a single "Entity" table to "register" both Customers and Contacts, and use
that (Entity) table's ID instead of having separate IDs in each?

 

[nvishnu]

I was not describing this case. Let me put an exmple.

Customer A has a contact table coulmns like below
tblContact (for CustomerA)
ContactID
CustomerID (fk from tblCustomer
ContactFirstName
ContactLastName
while customer B may need an extra contact info like Age. Another customer
may have Age and DOB.
tblContact (for CustomerB)
ContactID
CustomerID (fk from tblCustomer
ContactFirstName
ContactLastName
Age

All i am saying is the number of fields for contact field may vary from
customer to customer. Assume that each client has different database (we
dont need to customerid as foreign key. Whats the best design in this case.
I have thought of 2 cases 1. Add the dynamic fields to the same table 2.
have a seperate table call table extension and append the fields in that
table with a foreign key in the Contact table.

Thanks for suggestion. I hope this time its clear,

Navin

 

[Jeff Boyce]

A couple observations...

Don't store Age. This is a calculated value, based on DOB. If you store
Age, you must also, to ensure data integrity, know when that age changes
(?!DOB?!) and have a routine that runs regularly (?daily?!) to update Age,
or have someone do the data entry/editing, ... or have data you KNOW is not
accurate.

If there are not too many fields (I'm not sure I caught how many you had per
contact) in aggregate, it seems like you could create a single table with
all those (depends on how many) and only FILL the ones that apply. If you
don't want the customers to see the fields they don't use, this could be a
relation table that lists valid combinations of customer and field. Then,
on a form that held all fields, only show the ones that are valid.

Just one person's opinion...

Jeff Boyce
<Access MVP>

 

[nvishnu]

I was just giving example for age. It can be anything. New fields are added
by the user through the application. So we have no idea about the number of
fields during design phase. I have seen many ERP applications achieve this.
My thought was

whenever someone adds a new field (with datatype, length and all those
stuff) add it to Contact table (in this table) as well as a meta data
repository where we store the field descriptions for the table. Application
can now read the number of fields from the meta table and render the
controls to the screen dynamically.

Any thoughts.

 

[Duane Hookom]

I would never add new fields in a situation like this. I have seen
applications where a table is used to hold this information in records
rather than fields. For instance assuming:
tblContacts
============
conConID primary Key autonumber
conFirstName
conLastName
conPhone

tblAttributes
==============
attAttID autonumber primary key
attTitle text with values like "Spouse Name", "BirthDate", "Hat Size"
attDataType store whether the attribute might be text or numeric or
date or whatever
attFormat

tblContAttributes
===============
cnaCnAID autonumber primary key
cnaConID link to tblContacts.conConID
cnaAttID link to tblAttributes.attAttID
cnaValue text (255) store the attribute values such as "Susan",
"3/21/1954","7 5/8"

This system allows you to set an unlimited number of attributes/fields for
contacts (or other tables)

 

[Jeff Boyce]

If you are looking for a mechanism by which a user can add any number of
fields, related to each other in indeterminate fashion, why not use a
spreadsheet?

As Access is a relational database, you only get the real strengths of the
product if you use it relationally. If folks can add a field without
considering either entities or relationships, why do you need a relational
database?

Good luck!

Jeff Boyce
<Access MVP>

 

[Lynn Trapp]

I have seen many ERP applications achieve this.

Oracle does this through the use of what they call Descriptive Flexfields.
They are Varchar2 fields of length 240 and, in most cases, have no
designated use. Virtually every table associated with Oracle Applications
has from 12 to 20 fields like this. Each company that implements Oracle
Applications can determine how they want to use these fields and activate
only as needed.

I would not typically recommend this type of design for a database, however.
In most instances, a company will end up with multiple columns in a table
that are used for nothing and, therefore, are always null. I would much
prefer to sub-class those attributes into another table.