Showing a different fieldon each record!

[Plarent]

I have designed two tables. One of them holds, as records, fields for the other table. So, for example, I have Table1 with ID, FieldName and DataTypeID. The records in this table have a unique ID and then a string value like FirstName, LastName, Phone Number etc and Int, char or datetime. Remember these are records rather then fields. The second table has these fields: ID, FieldID(this is the connecting field to the other table) and NumericValue, TextValue, DateValue. I want, in a view, to show the respective value for each record and nothing else. So, if FirstName is a char and age is an int, I want to show, ID, FieldID, FirstName, CharValue in the first record and ID, FieldID, Age and IntValue in the second record and not the other fields (like DateTime value, Text Value etc).
Thanks,

Plarent

 

[AtomicWedgie]

hmm i don't find your question very clear.

could you provide an example

 

[Plarent]

Thanks for showing interest in this and sorry for the confusion.
Here is how the tables look like.
ID FieldName FieldType
1 LastName1 Char
2 FirstName1 Char
3 BirthDate1 DateTime
4 Age Number

The Other Table Looks Like

ID FieldID CharValue DateTimeValue NumberValue
1 1 Doe Null Null
2 2 John Null Null
3 3 Null 5/8/71 Null
4 4 Null Null 32

I want the View to look like this

FieldName Value
LastName1 Doe
FirstName1 John
BirthDate1 5/8/71
Age 32

Is this clear enough?

Thanks,

 

[SemperFiDownUnda]

You are building a DBMS ontop of a DBMS?

I take it you want to hold dynamic metadata?

your second table is VERY wasteful. Better to have 1 Value field then based on the "FieldType" do a Convert/Cast. You can have your SELECT statement use the a CASE statement to do this. But at the endo of the day your final table is still 2 columns that are both string data so you don't even have to use the Case statement.

I guess a "Why do you want to do this" is in order to better help you.

 

[kishleo]

well, why are you using 3 seperate columns in your second tables for each data type? you can modify your second table to have only 3 coulmns( ID,FieldID,Value) insert ages also in varchar format, i dont think so you will be having some difficulties in handling integers saved in varchar. just you have to use a convert/cast statment with the help of isnumeric
and now join your two tables on fieldid and you will get your output,....

Kishore MCDBA

 

[Plarent]

Thanks for your replies. I am doing this for a web application where the users (basically their adminitrator) can add fields on the fly and show them in the application.
As far as having separate fields for different data types, I imagined it would be the easiest way to query information.
You gave me a very good idea on how to put this all together.
I thought, I would have all the settings for the database in tables therefore giving the user as much power as possible. What I mean is, if they want to add a field to the application, they will add a field to the field definition table, than add this field to the appropriate area (Look above to table 2) and then, again, add this field to the screen layout table. Is there a different way of doing this that gives as much power to the user, and at the same time, is more efficient?
Thanks,

 

[kishleo]

well in this case you can alter your second table... like
instead of having seperate column for each datatype just add one column datatype and enter your datatypes as the records
so your columns in second table will be,

ID,FieldID,DataValue,DataType
1 1 Doe varchar
2 2 John varchar
3 3 5/8/71 datetime
4 4 32 numeric

Kishore MCDBA