Review of my database so far

[Mikebyrne]

Could someone offer suggestions on how to improve the database or the general structure of it?

All suggestions are welcome!

(Its an online retail project selling DVD's, CD's & Games)

 

[rsmonkey]

Had a quick browse and in general you have a good grasp of normalisation etc... however can i ask what the Temp user table is for in perspective of your system. what i would do is scrap the temp user table and incorporate that into you user table and have a additional column in the User table that identifies the type of user i.e Temp, Permanent etc...

 

[Mikebyrne]

The Temp user table is a holding area so that when a user confirms via an email link their detials will be passed into the users table

The one problem I can see is that I'm not sure if users can buy more than one item on a single vist with my current layout

Any suggestions?

 

[mdschuetz]

With the 1 to Many relationship you have established between Orders and Order Details, having more than 1 item on the order should not be an issue.

 

[Mikebyrne]

Thats what I originaly thought but someone suggested otherwise so I'll have to test it out.

Other Issues:

1) Should I merge the 3 product tables (CD, GAME, DVD) into one table??

2) Is having the password table seperate a better way of doing it as opposed to having the user details and password on the same table?

 

[Rabbie]

I would merge the 3 product tables provided they have a lot of common fields(With my eyesight I found your diagram a little hard to read).

 

[Mikebyrne]

I'll put a copy of the DB up too for anyone that wants a look

The only problem id have with merging the tables is the empty fields, it probably isnt good practise leaving fields blank but on the other hand it isnt good practise having similar fields on tables

 

[neileg]

A blank field in Access takes up no space, so within reason, there's no issue about blank fields within a record.

 

[mdschuetz]

It is fine the way it is, your data is more organized this way. I was taught that even if a field is blank, access still looks at it when it runs a report, query, or the field is called on in an event procedure and once your database gets large, having too many blank fields can adversely affect performance.

 

[Mikebyrne]

Ok, I've done a revised DB with the merged tables. Take a look and give me your opinion