Search code examples
sqlsql-serversql-server-2008dynamicrdbms

What better way to structure my database?

Need to identify the best way to contemplate this scenario : The User can enter the system which fields he wants and what type of field

Example: 

ID;Key;Type;
01;Name;Varchar;
02;Date;DateTime;
03;Gender;byte;

Then a 'tbRegister' table must store the information of the document registered by the user using the fields that it created.

Exemple:

 Guid;DocumentID;Fild_Name;Fild_Date;Fild_Gender;CreateOn; CreateBy;
das215sa-15d1a-2d56as1;1;João;21/01/2001;1;30/11/2013 10:00:00; msantiago;

I also see a relationship between these tables. Something like:

FildByDocument:

idFild;DocumentID;
01;1;
02;1;
03;1;
01;2;

How would this in practice? Like to meet a good structure. For such systems may contain very different types of documents

Got an exact example of what I wanted:

enter image description here

Thank you

Solution

  • I would do something like 

    Table_One_User

User_ID;INT; IDENTITY(1,1);  --<-- Primary Key
01;Name;Varchar;
02;Date;DateTime;
03;Gender;byte;


Table_Two_File

DocumentID;      --<-- Use INT not GUID (GUID is Not a good choice for Primary Key)
Fild_Name;
Fild_Date;
CreateOn; 
CreateBy;  Foreign Key --<-- Referencing to User_ID column in Table_One
                          -- You dont need to record Gender here  you have 
                          -- this information in Table one