Unknown additional fields...
-
Oh, I see. I didn't understand it that way.
I had thought more like this: for example, the number of screens would only apply to one client, like the phone number. While a field labeld "Screens" might be filled in for many clients, each record would have a one-to-one relationship with a client, again, just like a phone number.
You may be right.
-
Mark,
I hate tables such as this however here it goes.
You can build a table that would contain (and I NEVER call things JUST [ID]) the following example fields:
ContentID (AutoNumber / bigint)
PointerID (bigint) – Contains customer Account number, vendor ID, ect that record belongs to
PointerTypeID (int) – Tells the system what type of PointerID is here. You may have a vendorID the same as a CustomerID as an example, you would need to know who this belongs to
ContentClassID (Tie in to L_ContentClass table, see below)
ContentTypeID (Tie in to L_ContentType table, see below)
Content ( varchar (1000) ) ** Assuming that you are not talking larger data
** Lets call the above table L_Content
** You could go further with a ContentClass, and a couple of other identifiers that allow you to statically filter / drill / define the data in a record; intended to point to its anticipated use.
Now. You would have a table (I use L_ = Lookup tables; just a naming convention) called L_ContentType
In this table you would have the following fields:
ContentTypeID (Identity / INT)
ContentTypeDesc (A Description of the content type) / ( varchar (30) )
ContentTypeToken ( char(2) ) – Single letter token that you will use in sproc’s to determine what set of logic to use.
The L_ContentClass table would have:
ContentClassID (Identity / INT)
ContentClassDesc (A Description of the content class) / ( varchar (30) )
ContentClassToken ( char(2) ) – Single letter token that you will use in sproc’s to determine what set of logic to use.
L_PointerType table contains pointers to define the source of the record (the owner type).
PointerTypeID (int) (Identity)
PointerTypeDesc ( varchar(30) ) – Description of the Pointer
PointerTypeToken ( Char(2) ) – Token used for business logic.
We might have data for the L_PointerType table such as:
PointerTypeID PointerTypeDesc PointerTypeToken
1 Customer Record c
2 Vendor Record v
4 Web form inquiry wf
Ect….
In the L_ContentType table you would have records such as:
ContentTypeID ContentTypeDesc ContentTypeToken
1 varchar data v
2 int data type i
3 bigint data type b
4 Date Data d
Ect…..
In the L_ContentClass table you might have things like:
ContentClassID ContentClassDesc ContentClassToken
1 Form Answer Data f
2 Date data d
3 Address 1 data a2
4 Comments input c
Now that you have this simple structure, and I would add a couple things actually you could have something like this in your (blob) table. I will make it single column as going across would be too wide in this forum and confusing.
ContentID 1
PointerID 227364
PointerTypeID 2
ContentClassID 2
ContentTypeID 4
Content 07/27/3007
Another record for same table
ContentID 2
PointerID 542345
PointerTypeID 1
ContentClassID 1
ContentTypeID 1
Content I am 4’ tall with Blue eyes
Anyway you see the point. Now in a stored procedure you need to know how to get the information.
If you are populating data for a grid, containing answers to questions you might select form this table like this.
SELECT Content
FROM L_Content as c
JOIN L_PointerType as pt on pt.PointerTypeID = c.PointerTypeID
JOIN L_ContentClass as cc on cc.ContentClassID = c.ContentClassID
JOIN L_ContentType as ct on ct.ContentTypeID = c.ContentTypeID
WHERE pt.PointerTypeToken = ‘c’
AND cc.ContentClassToken = ‘f’
AND ct.ContentTypeToken = ‘v’
AND c.PointerID = 542345
The Above will retrieve from your BLOB table the Form answer results belonging to the CUSTOMER having account number; 542345
This format allows you to be as open as you want. The down side is that the table can get large and if you start relying on it for basically all of your data you will find yourself with inner joins, creating locks and all kinds of nasty performance issues.
If your system is not real busy, and you keep the use of this formula to those unaccounted for items that can not be inserted reasonably into your present architecture then you will be in good shape and it will serve you well.
With lots of multi-user use I would add WITH (NO LOCK) hints to the lookup table queries.
Hope you find this useful.
Jef
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
-
Thank you everyone for your help! Some very interesting advice.
Thanks again!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply