table design

[ayaznoor]

hi everybody, ok so im new here, and new to access as well. im working on an academic project, in which i have to build a database for Video shop that deals in CDs.




This is the basic guideline of the project:

Create a database of a CD shop which contains CDs’ like


Drama
Music
Movies
CD shop has a membership program. A customer can become a member after paying a registration fee of one time $20.A member will be given a unique ID with a special privilege of


10% discount on purchasing 2 CDs’
15% discount on purchasing 3 CDs’
And 20% discount on more than 3 CD purchase.


Non members have to pay the full amount. Every detail is being recorded in the database.

also note, i have to put in fields for movie category, director and release date, to answer the queries required to make.


any idea how should i go about it?


any help will be appreciated. thanks

[recyan]

A CD shop
selling zero, one or many CD's
belonging to different CATEGORIES
to MEMBERS and NON-MEMBERS
CUSTOMERS.
MEMBER CUSTOMERS offered discounts.
NON-MEMBER CUSTOMERS offered no discounts.

To start off, till someone comes along, just a few thoughts at a basic level:

Tables :
tblCD;
tblCDInventory; (To keep track of the same CD being purchased in different lots with perhaps different prices)
tblCDPrice; (Assuming the same CD might have different Prices over a period of time - perhaps could be incorporated in tblCDInventory)

tblCategories;

tblCustomers;
tblCustomerTypes;

tblOrders;
tblOrderDetails;


perhaps,

tblDiscounts

Need to think, how deep the project needs to be?

Thanks