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.

 

[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]

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

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

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.

 

[Chris Shepherd]

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.

 

[Chris Shepherd]

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.

 

[Chris Shepherd]

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.