Multi domain webshop database design

[pxChuck]

Hi!

For a school assignment I've been requested to create a multi domain webshop, this actually means that I'll have to write a webshop (with only one database) for multiple domains, every product could be displayed on multiple domains with
different properties, for example:

(Product) Flower
(Domain 'A' properties)
Minimal quantity: 100
(Domain 'B' properties)
Minimal quantity: 3

Of course, there's a lot more properties, but hence, it's only an example.

I've decided to use the (fairly new) FuelPHP framework, and of course MySQL, I already drawn up a database design (which is by no means complete), but I'm looking for some feedback, pointers and advice, you'll notice that the Customer and Employees tables are very redundant, for instance, there are a lot of columns that can be placed in a seperate table and inherited by the Customer and Employee tables.

Anyways here is the design: http://www.imgdumper.nl/uploads5/4ef3c2fe26782/4ef3c2fe11760-diagram.png

So if any of you can give me your opinion, (helpfull!) critism and advice, its greatly appreciated.

Thanks for your time ;-)

pxChuck.

- Woo! My first post! =]

NOTE: The column types aren't very specifics yet, still considering the best types - but I don't want to waste too much time if it turns out the design is a bust.

 

[CEpeep]

Welcome to [H]. If you haven't already, check out this thread: http://hardforum.com/showthread.php?t=810619

Looks like a decent starting point for a DB design. As you mentioned, there's redundancy in the Customers, employees and users tables. I would just have one users table containing everyone and then some sort of permission flag to designate if they are are a customer or an employee. I'm not sure I understand the difference between the Products and Categories tables. Categories contains names, descriptions and images. These are properties I would expect Products to have, from your description of the problem.

 

[pxChuck]

Welcome to [H]. If you haven't already, check out this thread: http://hardforum.com/showthread.php?t=810619

Looks like a decent starting point for a DB design. As you mentioned, there's redundancy in the Customers, employees and users tables. I would just have one users table containing everyone and then some sort of permission flag to designate if they are are a customer or an employee. I'm not sure I understand the difference between the Products and Categories tables. Categories contains names, descriptions and images. These are properties I would expect Products to have, from your description of the problem.

Hey. thanks for the reply!

Indeed I have checked that thread. I'm not the kind of guy that'll ask you guys to write things for me, I'm only asking for advice, (helpfull) critism, thanks for the heads up though. ;-)

I was also thinking about one User table, but I feared it'll be alot of data for one table, if I were to do one User table, I'll probably create another table to hold privileges with like you said permission flags.

Your correct about the products and categories table. The categories table is primarily for helping people to quickly seek products in a certain category (whether this is through a search function, or simply clicking on a category).

The products table is missing a few columns, I wasn't sure on how to do that yet, I'm thinking, about adding, 'pictures', 'description' and such to the Products table - with the possibility it'll be overwritten by the product_properties table, I'll have to consider my approach for that a bit longer.

Thanks again,

pxChuck.

 

[Shyne151]

I know you didn't ask for advice on your framework choice... but I would pick something more established like codeigniter or cakephp... both have excellent communities and good documentation.

As far as the database design...
The obvioususer data redundancy has already been pointed out.

As far as your products table. Why are you including the domain_id in both it and the properties table?
It seems as if you are going to have a lot of redundant data in your products table(ie: a product record for every site for every individual product). I would store the product_id in the product properties table and get rid of the property_id from the products table. Hopefully that made some sense... haa I just woke up.

 

[pxChuck]

Shyne151 thanks for the reply.

I just read up on CodeIgnitor (I've used it briefly, for nothing fancy, the project didn't work out) and CakePHP,
I'll look into those a little more and compare them to my needs and see which fits best. Thanks for the suggestion.

I primarily liked FuelPHP because it is fully PHP 5.3, and it's just plain simple. - but perhaps it doesn't fit my needs exactly.

I was still uncertain how to proceed with the Product, Product Properties and Domain tables.

Let me explain my thinking proces (best is to di this through a hypothetical scenario):

If I have 500 products, it'll mean (as things are as they are now) 5 (domains)x500=2500 entries in the Products and Properties
table, which is simply to much and far to redunandent.

Based on your input, I got thinking, a better approach would be:
> Product ID (pid) and Domain ID (did) equals Properties ID

for example, pid:1, did:1 then the properties ID would become: 11
for did: 2 it'll be: 12, of course the ID wouldn't become auto incremental/serial in the properties table then.

As for the redundant data for the products, I don't see a way to avoid it, since, every domain has different properties such as:
- Different language,
- Wholesale (requires a higher minimum quantity).

If anyone has some advice on this matter or on my approach, please let me know. ;-)

Thanks,

pxChuck.

 

[Shyne151]

Shyne151 thanks for the reply.

I just read up on CodeIgnitor (I've used it briefly, for nothing fancy, the project didn't work out) and CakePHP,
I'll look into those a little more and compare them to my needs and see which fits best. Thanks for the suggestion.

I primarily liked FuelPHP because it is fully PHP 5.3, and it's just plain simple. - but perhaps it doesn't fit my needs exactly.

I was still uncertain how to proceed with the Product, Product Properties and Domain tables.

Let me explain my thinking proces (best is to di this through a hypothetical scenario):

If I have 500 products, it'll mean (as things are as they are now) 5 (domains)x500=2500 entries in the Products and Properties
table, which is simply to much and far to redunandent.

Based on your input, I got thinking, a better approach would be:
> Product ID (pid) and Domain ID (did) equals Properties ID

for example, pid:1, did:1 then the properties ID would become: 11
for did: 2 it'll be: 12, of course the ID wouldn't become auto incremental/serial in the properties table then.

As for the redundant data for the products, I don't see a way to avoid it, since, every domain has different properties such as:
- Different language,
- Wholesale (requires a higher minimum quantity).

If anyone has some advice on this matter or on my approach, please let me know. ;-)

Thanks,

pxChuck.

Codeigniter works fine on php 5.3(we have multiple installs running on it). It however doesn't support 5.3 specific features. Is there features from 5.3 that you need? I was hesitant at first about ci... but was sort of forced to start using it at work. I haven't looked back since.

If I get time when I get out of work I'll install visio and map out how I would do the product table(s).

 

[pxChuck]

There are no specific 5.3 functions I require, though I liked the idea of a fully featured 5.3 framework, hence - if you look at the framework's code. It's just plain beatifull.

I've read that CI is faster than CakePHP. I'm unsure which framework I'll use, I'll have to read up about it some more, I'll let you know when I've made up my mind ;-)

About the product tables map, that'll be greatly appreciated - I've googled nuts, but haven't found anything solid to use as a comparison of different ways.

Thanks,

pxChuck. ;-)

EDIT: Did I say that you guys rock yet? 'Cause, you guys do! ;-)

 

[pxChuck]

Hi!

I've just modified my diagram, changed some stuff, added some stuff. Here it is:

http://www.imgdumper.nl/uploads5/4ef4cbfe06946/4ef4cbfdf0b5f-Diagram_2.png

To explain a few things I've added these tables:
- Watchlist (if something is out of stock, a customer can be notified when it's been refilled);
- Sessions (So that php sessions are saved in the database);
- Backlog (So that changes may be reverted, egg. Product has accidently been deleted (egg. drunk worker who had no business being logged in)).
- Newsletter stuff (You gotta love it..)
- User groups

Things that changed:
- Customer, Employee and user table are now one 'User' table.
- Changed/added a few fields from Products, properties and domains.

Still a few things missing (such as saving search parameters, to improve customer sales we should record those... or so I hear).

Anyway, again asking for opinions, feedback.

Thanks,

pxChuck.