The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database Design

So if i am selling product A (PA), service A(SA) and Gift Voucher A(GA)

I would have on my invoice Item 1 = PA, Item 2 = SA, and Item 3 = GA.

i dont know where to record this purchase transaction, in a table called transactions, and if so how is that assoaiated with the 3(maby more) different types of items

or do i only record payments made on the invoice in transactions and then have each item type track sales via a many to many relationship

should i use a table called items and associate them with invoices/transactions and products?
Frank R
Thursday, October 26, 2006
Speaking personally I would have two or three tables here

You should have either a record of each voucher issued, or an algorithm for calculating that the voucher code is genuine.

When the voucher is redeemed, then you record the fact.

One for the transaction recording
---id number for transaction
---who it was from
---link to delivery address
---Flag for has this transaction been processed

Once the transaction as created then the items and Services cans be listed in table number two (e.g. transaction_details) with
---an id number for each entry
---a code for Item or SERVICE
---the id number for the look up of your item or service
---either the voucher code, or the id of the voucher on your voucher table if you use one

Thus your transaction table can pull in personal details, date, method of payment and items from the accociated tables.

But that's possibly an old fashioed way of thinking. Interesting to see what others suggest
Colin Send private email
Thursday, October 26, 2006
The classification type of the product belongs in your products table.

When you sell something, you have the transaction, and the product number/id (or however you do this) will be stored in this transaction table.

However, you do NOT need to store the product classification type with each transaction, since you can join you way back to the products table to get the classification type.

Not only do you eliminate the repeating data (so, this is more normalized), but if you accident had classified a toaster as a service, and it should have been a product, then you can simply change this setting in ONE PLACE (that would be in the products table). When change this classification, then all of your reports etc. would reflect this change, and no “update” of existing data would need to occur to fix this mistake. So, keep the product classification in one place, and ONE instance of it will be stored.

Further, by having the classification in the products table, you can add new class types over time (and, of course, really, we would make a classification type table also!!

Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal Send private email
Sunday, October 29, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz