Hi all,

I'm new to databases, but I'm designing a rather complicated database for the stocks of instruments for my CCA, band.

Could you guys kindly help me out and give me opinions on whether this design is feasible?

Instrument Types
- (PK)Instrument type ID

Instruments
- (PK)Instrument ID
- (FK)Instrument type ID
- (FK)User ID
- Serial Number (unique but cannot be primary key)
- Brand
- Model Number
- Date of purchase
- Condition
o Last stock check date
o Stock check remarks
o Last maintenance date
o Maintenance remarks
o Overall condition (radio button - Everything OK, Still usable, Needs repair)

Users
- (PK)User ID
- (FK)Instrument ID

Suppliers
- (PK)Supplier ID
- Address

Instrument Orders (Instruments-Suppliers relational table)
- (FK)Instrument ID
- (FK)Supplier ID
- (FK)Date of purchase [display based on the entry with the corresponding Instrument ID]
- (FK)Serial Number [display based on the entry with the corresponding Instrument ID]
- (FK)Brand [display based on the entry with the corresponding Instrument ID]
- (FK)Model Number [display based on the entry with the corresponding Instrument ID]

Many instruments can exist for one instrument type, but only one user can use an instrument at any one time (and vice versa). Also, different brands/models of instruments can be supplied from different suppliers.

So.. is this design feasible? And what free database solution should I use that would allow many users to maintain the database and yet support this design?

Thanks in advance.

It looks reasonable to me. Instrument Types could use a description column unless your ID *is* the description. I personally would take the user ID out of instrument and the instrument ID out of user and put the two into a Usage table. It will make mistakes less likely.

I disagree - there's a lot of work to go yet.
Agreed re Instrument ID, though for different reasons.
Instrument Orders has no PK, and does not appear to be required. If it is required, then the last four columns should not be shared with Instruments.
You should not reference users from instruments and instruments from users. Do you actually store any information about users? CAn you have a user with no instrument, or an instrument with no user?
Why can't you use Serial Number?

pootle flump ur codings are working excelent.

Hey everyone, thanks for the replies. I agree with the usage table one too.

Hmm yes there can be instruments with no user, but every user has one instrument. There are like extra ones lying around.

I'm not quite sure on how to do the Instrument Orders one... now that I think about it I think I should put a PK for the table (invoice number?), then sort of have records for every order etc, and then have price/date/all those stuff as keys in the table itself. So... the date of purchase should be a foreign key to the instrument table right?

Also, the serial number cannot be the primary key for some reason that the format of serial numbers differ for each instrument, and there might be clashes if all are tallied together, even though I said it was unique. I'm not sure, but I have a feeling that each instrument should be given its own unique ID instead..

Oh and actually I'm not sure whether to include data about users as this database isn't about the whole band, but rather just the stocks. Eh.. I forgot to include the user's name. Whoops.. :P

Here's what I have so far:

Instrument Types
- (PK)Instrument type ID
- Instrument name
- Section (woodwinds/brass/percussion)

Instruments
- (PK)Instrument ID
- (FK)Instrument type ID
- Serial Number (unique but cannot be primary key)
- Brand
- Model Number
- Date of purchase
- Condition
o Last stock check date
o Stock check remarks
o Last maintenance date
o Maintenance remarks
o Overall condition (radio button - Everything OK, Still usable, Needs repair)

Users
- (PK)User ID
- Name
- Class
- Phone number (perhaps?)

Suppliers
- (PK)Supplier ID
- Supplier name
- Address
- Phone number
- Operating hours

Instrument Orders
- (PK)Order (invoice) number
- Date of purchase
- (FK)Instrument ID
- (FK)Supplier ID

Usage table
- (FK)User ID
- (FK)Instrument ID

Ahh I'm quite confused... I think I mixed up views with tables, that's why I'm referencing things like that.. Am I doing things correctly?

Thanks so much (to both of you) for your help so far though!