looking for advice on best way to set up a databse for e commerce project

dreamstarusa

Hello there,

I 'd like to have you guys' opinion on the best way to structure a mysql databases for the following scenario that I am contemplating:

An E commerce website of downloadable content (music for the sake of this explanation). The user will submit application to have his/her "own store". Upon approval, the user is given unlimited amount of space for him/her to upload as many songs as possible in the hopes of selling them. He decides of the price and we just keep a small fee for maintenance of the site (credit card fees, merchant accounts, monthly statements delivery etc...).

To start, I am thinking of setting up 1 database with the following table : approved_artists, which would contain all their personal info as well bank info for payment. They would be able to log in and edit this information.

I am wondering about how to store their content... for simplicity but also for security purposes.
Should i have 1 database for each artist, or should I have 1 database for all my artists, and each artist gets a table with the following info: song title, description, price etc..

Any feedback in extremely welcome.

Thank you

Max

NogDog

One database with one set of tables.

One table that contains general info about an artist (one artist per row).

One table that contains one row per song (or whatever) to be sold. It includes a column which is a foreign key to the primary key of the artist table.

dreamstarusa

Hi Nog and thanks for the reply.

Forgive my ignorance, I have just started learning about databases....

Let me summarize:

1 database contains

1 table which I call 'store_owners' where each store gets an id number from me .. say start at 1000 so I would call the field 'storeID' and of course I would make that field 'primary key'. 1 row per store... each row has fields for storeID, address, email, bank information (for payment) etc...

1 table called 'store_content' where each row would be 1 product so there would be fields for 'song_title', 'description' 'duration' 'genre' 'price' etc... and then you would put one field called 'storeID'? this last one being foreign key... not sure what that means but is this like a combination of 2 fields to make a primary key?

Now.. is this second table not going to be HUGE? if I have 5000 stores owners (artists) who put 100 songs each as an example.... shouldnt' each store owner have its own table for their content?

NogDog

store_owner
================
store_owner_id  INT (PRIMARY KEY index)
...additional fields that define an individual owner...


store_content
=================
store_content_id  INT (PRIMARY KEY index)
store_owner_id    INT (non-unique index)
...additional fields that define a sellable item...

So each item that gets added to the store_content table must include the relevant store_owner_id value from the relevant store_owner table row in the store_content table's store_owner_id field. That field is then used in queries where you only want to select or update rows for a particular store owner, providing a many-to-one association between the content and owner tables.

Do not worry about number of rows: if you properly index the columns that are used in any WHERE or ORDER BY clauses, size is not an issue -- that's what databases are designed to do. (At work we often deal with data tables with hundreds of thousands of rows up to tens of millions of rows.)

dreamstarusa

Thanks Nog!
That's precious information!

I guess now is a good time to bring up the infamous topic: store image in database or store links???
See... I will have to have each owner submit 3 forms of IDs so i can pay them and they will submit scans... meaning images. Should I have that stored directly in the database as blob, or simply store the links where the file is stored?
I read about this topic and people are really divided!!!

Also, I suppose I should make a table for 'owner_sales' since I will pay them out each month and i would like them to monitor their success by going into a control panel and looking at their sales. Any thoughts on the structures of that sales table?

bradgrafelman

dreamstarusa;11029491 wrote:
Should I have that stored directly in the database as blob, or simply store the links where the file is stored?

Here's my $.02: Store the image files on the file system, leaving the data related to those images to be organized and stored in the database.

dreamstarusa;11029491 wrote:
Any thoughts on the structures of that sales table?

One thought is: you haven't given us hardly any information about what data that table is to hold, so there's not much we can say about what its structure should or shouldn't be. Plan out what data you want to store, figure out how it relates to other data you're already storing, normalize the new structure, and then you're done.

NogDog

Normally I would always store image files on the file system. However, my first suggestion would be to figure out some way to not store these ID images, as you then become liable if that data is ever compromised and obtained by someone who should not have access to them. If you do have to store them, make sure you have some good access restriction and encryption processes in place on them.

Since presumably these ID documents would rarely be accessed, after initial account creation, I might consider storing them (encrypted) in the database -- though likely not -- but make sure that once the account is verified that you delete those images (files or database fields) ASAP after that verification is done.

dreamstarusa

great advice!
Yeah, it's a risk to have such important documents in storage.. Like you said... you don't want anyone to come across that!!!
BUT, I do feel like I should keep them... in case I'm audited and I have to support my claims of having paid all these folks. I would imagine I would have to show some kind of proof... well, let me think about it. From what you said though, it looks like if i do decide to keep them, you are advising on actually storing inside the database... would that be as a blob? where can I find information on how to upload images inside the database with ENCRYPTION?

Going back to my 'store_owner' table, should I have fields for username and password in it, or you this be a separate table linking to studio_owner?