Review of database - Suggestions welcome

[Mikebyrne]

Hi,

I'm doing a database for a CD, DVD, Game website could you take a second to review it and give me suggestions to improve things.

I've included my old and revised database to see what if done

 

[rsmonkey]

as i said before get rid of the temp table... everything for that table can be done in the users table, simply create another column in the users table which defines the user as Temp/Member etc... personally i would have kept the CD/DVD/Games in seperate tables just so if this system was going to be used in an online system it would be easier to create genre's, sub genre's etc. for navigation. I.E a DVD say Lethal Weapon 4 can be genre into Action and Sub Genre is Comedy etc etc. balh u catch my drift. That can still be done in youre current structure I would personally prefer the organisation... maybe consider a junction table aswell for many to many relationships such as a user purchasing the same dvd twice. Apart from that & most of which are optional it looks good

 

[Mikebyrne]

Thanks for the input, I'm agree with you about the 3 tables but think the guy over my project wants it in 1 table. (dont know why!!!!) I prefer 3 tables.

Do you think having the password on a seprate table is right?

 

[Rabbie]

I think you can manage fine with either 1 or three tables for the cd/dvd/Game. I think you should merge the temp table with the user table. I would keep the passwords in a separate table because it makes it easier to limit access to this.

 

[Mikebyrne]

How would you suggest I merge the temp into the users table??

 

[Simon_MT]

I would definitely go for one table. I have a system with Originals and Editions. The difference between the Originals and Graphics is that Graphics have Copies i.e. Stock Details and Originals do not. There was a very important structural reason for the this structure as the previous system did not have the concept of an Edition so all the Edition information had to be re-iterated on each and every Copy within that Edition. Non Normalised.

You will find having one table as rsmonkey suggested so much easier. If you were to write a paper based invoicing system your Invoice Details would need Union Statements to bring the three disparate tables together essentially just to get the Descriptors and Price.

With one table you only need one Query that gets everything and using Genre you know exactly what type of product is sold.

The multiple table does work but is much more intense to program and design.

Simon

 

[Mikebyrne]

My main concern with going the one table route was having blank fields but that seems to be ok

Does my order system look ok to you? ie Users being able to order multiple iems on one order etc

 

[Simon_MT]

There are a a number of fields common (in all but Name) to all three formats. Having blank fields is OK, but you can always create "X" in fields as a default. I saw somewhere that it more effiecient to put your sparsely used (blank) fields at the end of the table. I would put each format IDs First in your Tables.

It looks pretty good. Delivery Charges (Postage) could be added to Orders along with a Discount if you intend to offer an incentive for bulk purchasing.

I know this might see barking mad there are some people how make a case for on Contacts Table, to include Users, Clients, Employees and Suppliers. I think the rationale is not so much design and structure but simply for ease of use.

Simon

 

[Mikebyrne]

You've raised some good points Simon.

Thanks for all your help