Database Design: Is this Even Practical?

[Jonathan Wood]

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

[Eliyahu Goldin]

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" <jw***@softcirc uits.comwrote in message
news:u2******** ******@TK2MSFTN GP04.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

[Jonathan Wood]

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" <RE************ **************@ mMvVpPsS.orgwro te in
message news:eN******** ******@TK2MSFTN GP06.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" <jw***@softcirc uits.comwrote in message
news:u2******** ******@TK2MSFTN GP04.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

[Chris Shepherd]

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.

[Jonathan Wood]

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" <ch**@nospam.ch sh.cawrote in message
news:ui******** ******@TK2MSFTN GP03.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.

[Chris Shepherd]

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.

[Jonathan Wood]

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" <ch**@nospam.ch sh.cawrote in message
news:Oe******** ******@TK2MSFTN GP05.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.

[Chris Shepherd]

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.