Saturday, February 25, 2012

Database Design Question

I am right now in process of designing a database for hosting business. Now
just like other hosting companies even this hosting companies has its
different web hosting packages. But besides that the company is going to
provide a feature to customer where in they can select/customize the package
wherein they might want to add one of two additional feature that are not a
part of the standard package.
I have designed most of that tables but i am confused on how exactly should
i design the "User Defined Package" table.
There are one of the two things that i can do.
1) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have a "featureid" (integer datatype) column that
would be referencing "Features" table.
The problem here is that it would be easily be managable from programming
aspect but there wil be redundancy factor since if a same customer takes 5
additional features then there would be 5 rows with same customer id and
separate featureid and this is just for one customer. If there is a large
customer base it could create space issues as well.
2) Have a column "customerid" (integer datatype) that will be referencing
to the customer table and have another column featureids (varchar datatype)
that would have all the additional feature ids seperated by a deliminator.
There won't be redundancy in this case but would make things little bit
complicated from programming aspect since everything new additional feature
to to be added or edited or to be removed will require some work in the
code.
Which method should i go for that would be helpful not just now but also in
future as the customer base increases.
I do not have anything else in mind. If there is any other solution to this
all the suggestions are welcomed.
Thank you
NielNiel wrote:
> I am right now in process of designing a database for hosting business. Now
> just like other hosting companies even this hosting companies has its
> different web hosting packages. But besides that the company is going to
> provide a feature to customer where in they can select/customize the package
> wherein they might want to add one of two additional feature that are not a
> part of the standard package.
> I have designed most of that tables but i am confused on how exactly should
> i design the "User Defined Package" table.
> There are one of the two things that i can do.
> 1) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have a "featureid" (integer datatype) column that
> would be referencing "Features" table.
> The problem here is that it would be easily be managable from programming
> aspect but there wil be redundancy factor since if a same customer takes 5
> additional features then there would be 5 rows with same customer id and
> separate featureid and this is just for one customer. If there is a large
> customer base it could create space issues as well.
> 2) Have a column "customerid" (integer datatype) that will be referencing
> to the customer table and have another column featureids (varchar datatype)
> that would have all the additional feature ids seperated by a deliminator.
> There won't be redundancy in this case but would make things little bit
> complicated from programming aspect since everything new additional feature
> to to be added or edited or to be removed will require some work in the
> code.
> Which method should i go for that would be helpful not just now but also in
> future as the customer base increases.
> I do not have anything else in mind. If there is any other solution to this
> all the suggestions are welcomed.
> Thank you
> Niel
I recommend you study some books or take a course on database design
theory before you go further. Your option 2 is a textbook example of
how NOT to do it.
Your first option sounds right to me from the point of view of
scalability and integrity. Of course I haven't had the opportunity to
analyse your business requirements, I only have your narrative to go
on. That's why newsgroups are a poor place to get database design
advice.
--
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
--|||Thanks for the suggestion david,
I'll go ahead and have a look at few book for reference. Can you advise me
on any good places/tutorials on website that i can go through to get a clear
concept on this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1143574016.274320.221210@.v46g2000cwv.googlegroups.com...
> Niel wrote:
> > I am right now in process of designing a database for hosting business.
Now
> > just like other hosting companies even this hosting companies has its
> > different web hosting packages. But besides that the company is going to
> > provide a feature to customer where in they can select/customize the
package
> > wherein they might want to add one of two additional feature that are
not a
> > part of the standard package.
> > I have designed most of that tables but i am confused on how exactly
should
> > i design the "User Defined Package" table.
> > There are one of the two things that i can do.
> >
> > 1) Have a column "customerid" (integer datatype) that will be
referencing
> > to the customer table and have a "featureid" (integer datatype) column
that
> > would be referencing "Features" table.
> > The problem here is that it would be easily be managable from
programming
> > aspect but there wil be redundancy factor since if a same customer takes
5
> > additional features then there would be 5 rows with same customer id and
> > separate featureid and this is just for one customer. If there is a
large
> > customer base it could create space issues as well.
> >
> > 2) Have a column "customerid" (integer datatype) that will be
referencing
> > to the customer table and have another column featureids (varchar
datatype)
> > that would have all the additional feature ids seperated by a
deliminator.
> > There won't be redundancy in this case but would make things little bit
> > complicated from programming aspect since everything new additional
feature
> > to to be added or edited or to be removed will require some work in the
> > code.
> >
> > Which method should i go for that would be helpful not just now but also
in
> > future as the customer base increases.
> >
> > I do not have anything else in mind. If there is any other solution to
this
> > all the suggestions are welcomed.
> >
> > Thank you
> > Niel
> I recommend you study some books or take a course on database design
> theory before you go further. Your option 2 is a textbook example of
> how NOT to do it.
> Your first option sounds right to me from the point of view of
> scalability and integrity. Of course I haven't had the opportunity to
> analyse your business requirements, I only have your narrative to go
> on. That's why newsgroups are a poor place to get database design
> advice.
> --
> 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
> --
>

No comments:

Post a Comment