i have requirement in my project that i have a entiity called service that
has some basic attribute that we know for every service is same for eg
(service no,s name,service type ,sla) i have a table for service that includ
e
all the basic attributes , but the problem is every service can have some
advance attributes that are not know at design time for e.g new pc request
service has some advance attributes for this i have created two table one
that created advance attributes of a service ( table has , attribut name ,
data type e.g ) as a columns then second table has values for every advance
attribute uses
service table advance attribute table advance attribut value tab;e
service no att id att id
service name data type service no
service type name value
sno
etc
do v have better solution to this requiremnt
--
Regards
Adeel Alvi
92-300-2584597This is a design that we have also used wherein the main attributes are
placed in a regular table, but the unknown attributes are placed in another
table which has an FK (to the primary table), a AttributeName column, a
DataType column and a Value column. However, one issue you might have is on
the datatypes. You can either have 1 place holder column for each data type
and then place your values accordingly or always use (N)VARCHAR.
Incidentally, we are thinking of moving such a design into XML. Since these
are unknown attributes and can keep changing, we can model this as an XML
document and store it in the database and then use it for processing. If you
are using SQL Server 2005, XML is a native data type and you have XQuery to
use for processing. In SQL Server 2000, you have to get it to the
application tier to do the processing.
This is how we have modelled it, but others in this group might have other
ideas.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"AdeelAlvi" <alvi_adeel@.hotmail.com> wrote in message
news:11060E87-5878-4AB1-B749-57B4AD7EFD89@.microsoft.com...
>i have requirement in my project that i have a entiity called service that
> has some basic attribute that we know for every service is same for eg
> (service no,s name,service type ,sla) i have a table for service that
> include
> all the basic attributes , but the problem is every service can have some
> advance attributes that are not know at design time for e.g new pc request
> service has some advance attributes for this i have created two table one
> that created advance attributes of a service ( table has , attribut name ,
> data type e.g ) as a columns then second table has values for every
> advance
> attribute uses
> service table advance attribute table advance attribut value tab;e
> service no att id att id
> service name data type service no
> service type name value
> sno
> etc
> do v have better solution to this requiremnt
> --
> Regards
> Adeel Alvi
> 92-300-2584597|||AdeelAlvi wrote:
> i have requirement in my project that i have a entiity called service that
> has some basic attribute that we know for every service is same for eg
> (service no,s name,service type ,sla) i have a table for service that incl
ude
> all the basic attributes , but the problem is every service can have some
> advance attributes that are not know at design time for e.g new pc request
> service has some advance attributes for this i have created two table one
> that created advance attributes of a service ( table has , attribut name ,
> data type e.g ) as a columns then second table has values for every advanc
e
> attribute uses
> service table advance attribute table advance attribut value tab;e
> service no att id att id
> service name data type service no
> service type name value
> sno
> etc
> do v have better solution to this requiremnt
> --
> Regards
> Adeel Alvi
> 92-300-2584597
The best solution is good business analysis during design followed by
good change control when you go live. In a corporate environment end
users are not the best people to decide how to add new attributes.
Users won't think about functional dependencies or validation or
encoding conventions or conformance to internal standards. Those things
are best left to database professionals. If your database development /
administration team takes ownership of change control then you can just
continue to add new columns in future.
If you are a software vendor then the considerations are different.
You'll probably want to add some "loosely-typed" structures such as you
have proposed.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||dear SriSamp
we are using sql server 2000
if we use xml approach , then what will be the right approach beacuse then
advance attribute to a particular service will be defines one time and then
it will be used for transaction that we normally do in usuall senario first
creating the table then inserting the record in it . one thing to mentioned
is that we design custom forms also on our advance attribute that are used
for service request , for e.g new pc request service will have some advance
attribute like sno model manaufacture vender configuration etc that will be
used in new pc request form .
will you plz tell me the right approach to move ahead
--
Regards
Adeel Alvi
92-300-2584597
"SriSamp" wrote:
> This is a design that we have also used wherein the main attributes are
> placed in a regular table, but the unknown attributes are placed in anothe
r
> table which has an FK (to the primary table), a AttributeName column, a
> DataType column and a Value column. However, one issue you might have is o
n
> the datatypes. You can either have 1 place holder column for each data typ
e
> and then place your values accordingly or always use (N)VARCHAR.
> Incidentally, we are thinking of moving such a design into XML. Since thes
e
> are unknown attributes and can keep changing, we can model this as an XML
> document and store it in the database and then use it for processing. If y
ou
> are using SQL Server 2005, XML is a native data type and you have XQuery t
o
> use for processing. In SQL Server 2000, you have to get it to the
> application tier to do the processing.
> This is how we have modelled it, but others in this group might have other
> ideas.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "AdeelAlvi" <alvi_adeel@.hotmail.com> wrote in message
> news:11060E87-5878-4AB1-B749-57B4AD7EFD89@.microsoft.com...
>
>
)