back end database design assistance

[exphtur]

I am looking for a bit of guidance please with the design of a simple back end database in Microsoft Access to manage the customer order processing system for a small online fitness equipment store selling treadmills/steppers/rowers and weights machines.

I need to get my ER model to 3NF and also work out the cardinality and participation correctly. The following is what I have managed so far.

Entity: tblCustomer
Attributes: CustomerID(PK)
FirstName
LastName
Address 1
Address 2
Telephone
Fax
Email
UserName ( to log into the web site)
Password

Entity: tblEquipment
Attributes: ProductID(PK)
Product Description
Product Photo

Entity: tblOrder
Attributes: OrderID(PK)
CustomerID(FK)
OrderDate(PK)
ProductID(FK)
CategoryID(FK)
Quantity

Entity: tblCategory
Attributes: CategoryID(PK)
CategoryName (ie steppers/rowers/treadmills)


Relationships between tblCustomer - tblOrder (1:N)
between tblOrder - tblEquipment (1:N)
between tblCategory - tblOrder (1:N)

Do I need any other tables or adjustments to what I have at the moment ? Any suggestions would be very much appreciated. Thanks

 

[Golom]

If I were to make any adjustments I would note that in most Order systems there are two tables for orders ... an Order Header and Order Lines. Your construct would allow a customer to order only one item per order. That causes a bit of a normalization issue in the sense that things like "CategoryID" and "Quantity" are really attributes of "ProductID" ... not of the primary Key. In this specific instance that will probably not get you into trouble because the information is historic and, once recorded, should never change.

The foreign keys between "tblOrders" and "tblEquipment" and between "tblOrders" and "tblCategory" could cause you problems. At the time the order is created you will presumably resolve product and category information by lookups to those tables. However, if you enforce referential integrity with that foreign key definition then you will not be able to remove a product from tblProducts with (Delete Prevent) or you will modify or remove records in historical orders (Cascade) if you change an item in tblEquipment and/or tblCategory ... probably not a good thing.


I do note that your "tblOrder" has "OrderID, OrderDate" as the primary key. The implication of that is that the whole key (i.e. both fields) are required to uniquely identify an order. If you are assigning Order Numbers as ever-increasing values then OrderID alone should suffice as the primary key.

If on the other hand, you restart order numbering each day then both fields are needed. That's probably not good practice from a business perspective. Having to refer to both an order number AND the order date to identify the order is not a usual business practice.

Finally, I note that no prices are to be found in your system. Most systems like this are about selling stuff. Is that not the case here?

 

[LittleSmudge]

I need to get my ER model to 3NF and also work out the cardinality and participation correctly.

WHY ?

Do you have a manager that knows what these terms even mean ?

Is it an issue of 'professional pride'

Or is it because thats what the college course work says you have to do ?


There are, in reality, bigger issues at stake in a REAL world database than doggedly sticking to 3nf etc.. .. ..





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[BNPMike]

Whoa there smudge, I was just enjoying that!

exphtur
What does participation mean?

 

[LittleSmudge]

What does participation mean?

Probobly means 'partitioning'



From the lack of a response it seems like option 3 might have been close to the mark !




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[BNPMike]

Shame, I could could do with a new word.

I'll just have to go and find out what 'relvar' means.

 

[DanJR]

Given that ER was mentioned, I would guess that 'participation' refers to "participation cardinality constraints. 'participation' is just one type of 'cardinality'.

But, i've never heard of applying a logical modelling concept to a conceptual model.