Database Design: Is this Even Practical?

I've been given a spec that seems unwieldy to me. As I'm pretty new to DB
design, I would appreciated any input this spec.

The site has three types of users: ADMIN, RESELLERS, and CLIENTS. The
database will be storing ITEMS and COLLECTIONS (COLLECTIONS are lists of the
ITEMS).

First, ADMIN users are to be able to create a MASTER LIST of items.

Next, RESELLER users are to be able to maintain a list of ITEMS that
includes all those from the MASTER LIST in addition to custom ITEMS that
they create. In addition, they can create custom COLLECTIONS that include
any of those items. RESELLERS can then assign any number of these
COLLECTIONS to their CLIENTS.

Finally, CLIENTS are to be able to customize the COLLECTIONS assigned to
them, including the ability to delete and copy COLLECTIONS, and to add their
own custom ITEMS to them.

I hope that is reasonably clear. This just seems like a mess to me. I'm not
sure if there is a shortcut way to handle this. And I'm not sure if I should
tell my client that this approach is asking for trouble in terms of server
use, and maintainability.

Thanks.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Re: Database Design: Is this Even Practical?

The requirements are reasonably clear. You just need to design the database
carefully to cater for the required level of flexibility. If you are not
experienced in database design, it may be indeed easy to mess it up, but not
because the requirements are not good. It could be a good idea to ask
someone to help you with the original database design. It won't be that
complex, once the schema is made and you understand it, you should be able
to proceed on your own.

--
Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net


"Jonathan Wood" wrote in message
news:u2sCr0bnIHA.5208@TK2MSFTNGP04.phx.gbl...
> I've been given a spec that seems unwieldy to me. As I'm pretty new to DB
> design, I would appreciated any input this spec.
>
> The site has three types of users: ADMIN, RESELLERS, and CLIENTS. The
> database will be storing ITEMS and COLLECTIONS (COLLECTIONS are lists of
> the ITEMS).
>
> First, ADMIN users are to be able to create a MASTER LIST of items.
>
> Next, RESELLER users are to be able to maintain a list of ITEMS that
> includes all those from the MASTER LIST in addition to custom ITEMS that
> they create. In addition, they can create custom COLLECTIONS that include
> any of those items. RESELLERS can then assign any number of these
> COLLECTIONS to their CLIENTS.
>
> Finally, CLIENTS are to be able to customize the COLLECTIONS assigned to
> them, including the ability to delete and copy COLLECTIONS, and to add
> their own custom ITEMS to them.
>
> I hope that is reasonably clear. This just seems like a mess to me. I'm
> not sure if there is a shortcut way to handle this. And I'm not sure if I
> should tell my client that this approach is asking for trouble in terms of
> server use, and maintainability.
>
> Thanks.
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>

Re: Database Design: Is this Even Practical?

Well, I kind of *was* asking for help, as you suggested.

I think I have the design mostly worked out. But I do have some concerns
about performance once the site becomes busier and the database grows large.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com


"Eliyahu Goldin" wrote in
message news:eNj0jygnIHA.3780@TK2MSFTNGP06.phx.gbl...
> The requirements are reasonably clear. You just need to design the
> database carefully to cater for the required level of flexibility. If you
> are not experienced in database design, it may be indeed easy to mess it
> up, but not because the requirements are not good. It could be a good idea
> to ask someone to help you with the original database design. It won't be
> that complex, once the schema is made and you understand it, you should be
> able to proceed on your own.
>
> --
> Eliyahu Goldin,
> Software Developer
> Microsoft MVP [ASP.NET]
> http://msmvps.com/blogs/egoldin
> http://usableasp.net
>
>
> "Jonathan Wood" wrote in message
> news:u2sCr0bnIHA.5208@TK2MSFTNGP04.phx.gbl...
>> I've been given a spec that seems unwieldy to me. As I'm pretty new to DB
>> design, I would appreciated any input this spec.
>>
>> The site has three types of users: ADMIN, RESELLERS, and CLIENTS. The
>> database will be storing ITEMS and COLLECTIONS (COLLECTIONS are lists of
>> the ITEMS).
>>
>> First, ADMIN users are to be able to create a MASTER LIST of items.
>>
>> Next, RESELLER users are to be able to maintain a list of ITEMS that
>> includes all those from the MASTER LIST in addition to custom ITEMS that
>> they create. In addition, they can create custom COLLECTIONS that include
>> any of those items. RESELLERS can then assign any number of these
>> COLLECTIONS to their CLIENTS.
>>
>> Finally, CLIENTS are to be able to customize the COLLECTIONS assigned to
>> them, including the ability to delete and copy COLLECTIONS, and to add
>> their own custom ITEMS to them.
>>
>> I hope that is reasonably clear. This just seems like a mess to me. I'm
>> not sure if there is a shortcut way to handle this. And I'm not sure if I
>> should tell my client that this approach is asking for trouble in terms
>> of server use, and maintainability.
>>
>> Thanks.
>>
>> --
>> Jonathan Wood
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>
>

Re: Database Design: Is this Even Practical?

Jonathan Wood wrote:
> Well, I kind of *was* asking for help, as you suggested.
>
> I think I have the design mostly worked out. But I do have some concerns
> about performance once the site becomes busier and the database grows
> large.

Might I suggest that if this is a concern for you that rather than relying on a
response here, you simply write some queries to populate your tables with
several years' worth of data, then run some tests looking at different points in
the load history?

Practical testing tends to trump theory.

Chris.

Re: Database Design: Is this Even Practical?

Perhaps. I just thought someone here who may have had more practical
experience than myself might offer some thoughts.

Thanks.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"Chris Shepherd" wrote in message
news:uimhQ7wnIHA.5692@TK2MSFTNGP03.phx.gbl...
> Jonathan Wood wrote:
>> Well, I kind of *was* asking for help, as you suggested.
>>
>> I think I have the design mostly worked out. But I do have some concerns
>> about performance once the site becomes busier and the database grows
>> large.
>
> Might I suggest that if this is a concern for you that rather than relying
> on a response here, you simply write some queries to populate your tables
> with several years' worth of data, then run some tests looking at
> different points in the load history?
>
> Practical testing tends to trump theory.
>
> Chris.

Re: Database Design: Is this Even Practical?

Jonathan Wood wrote:
> Perhaps. I just thought someone here who may have had more practical
> experience than myself might offer some thoughts.

Well all you posted was some basic design elements, not organized in any way
that gives insight into how you would actually create the tables.

So break it down; After you normalize everything to your own satisfaction, how
do you split out the logical entities into the tables?
What technical constraints are you working with (disk space, processing power,
etc.)? What will you be permitting the users to do (for instance, searching on
non-key fields) that might alter how you should index the tables?

There are lots more things to consider apart from that as well that affect
performance down the road. If you provide more information (even generalized so
as not to directly be related to your project) you will probably get more
responses.

Chris.

Re: Database Design: Is this Even Practical?

Perhaps I'll just run with what I've come up with myself.

There's a certain type of discussion that I would've found quite helpful.
But it wouldn't be a discussion about disk space, processing power, etc.

Thanks.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com


"Chris Shepherd" wrote in message
news:OeGjAm8nIHA.4848@TK2MSFTNGP05.phx.gbl...
> Jonathan Wood wrote:
>> Perhaps. I just thought someone here who may have had more practical
>> experience than myself might offer some thoughts.
>
> Well all you posted was some basic design elements, not organized in any
> way that gives insight into how you would actually create the tables.
>
> So break it down; After you normalize everything to your own satisfaction,
> how do you split out the logical entities into the tables?
> What technical constraints are you working with (disk space, processing
> power, etc.)? What will you be permitting the users to do (for instance,
> searching on non-key fields) that might alter how you should index the
> tables?
>
> There are lots more things to consider apart from that as well that affect
> performance down the road. If you provide more information (even
> generalized so as not to directly be related to your project) you will
> probably get more responses.
>
> Chris.

Re: Database Design: Is this Even Practical?

Jonathan Wood wrote:
> Perhaps I'll just run with what I've come up with myself.
>
> There's a certain type of discussion that I would've found quite
> helpful. But it wouldn't be a discussion about disk space, processing
> power, etc.

Next time, if you want help, expect people to ask you questions to get a clearer
idea of what you're looking for, and be prepared to answer them.

Chris.