Fundamental database structure (1 Viewer)

Bobadopolis

No I can't fix it dammit!
Local time
Today, 09:59
Joined
Oct 6, 2005
Messages
77
Hi People,

I am building a database of in-car audio systems. Stored in this are simple things like vehicle information, audio brand and other general information. The main information I need to store includes details about speakers (position, quantity, material, range, size etc.) and amplifiers (power output, no. of channels etc.). Sounds simple I hope.

I have been thinking about this extensively and have come up with two solutions, each with its own pros and cons. My primary concern here is how the speaker and amplifier information is stored:

1) Tables specific to car regions (e.g. Rear Speakers, Front Speaker, Surround Speakers) have the fields 'Range', 'Material', Size, etc. Range and material can read their values from other look-up tables no probs. These would be linked to the main table via it's primary key in a 1-M relationship.
-Pros:
Ease of construction and general handling of data
Flexibilty/expandability (in terms of speaker quantity)
-Cons:
When it comes to data entry the form would need to be continuous and continuous forms I have found to be a pain in terms of referring to individual records!
I want some of these controls to conrol a diagram for my report (and input form) - such that they show/hide speaker pictures on a car diagram. Also, I have not yet figured out how to validate a continuous form in this situation or delete records from it either (should the user makes an error).

2) Again, tables specific to car regions but this time with a field for each speaker range (mid-range, tweeter, woofer, etc.) and it's associated data, tied to the main table via the main table ID in 1:M relationships.
-Pros:
Easy to manipulate form/report properties because every speaker has its own field
Easier to remove erroneous data (I imagine)
-Cons:
More complex construction (and less efficient) - e.g. material occurs more than once for each record so to look up material form a central pool requires an intermediate table
Many many more fields!
Non-expandable in terms of speaker quantity

Similar principals can be applied to amplifiers so I haven't mentioned those.

So what do you think? Or is there another way...

Hope that all makes sense!

Thanks for your time,

Bobadopolis
 

neileg

AWF VIP
Local time
Today, 09:59
Joined
Dec 4, 2002
Messages
5,975
One table for all speakers with a field identifies the location. So a car with 6 speakers would have six records in the table.

I don't understand your problem with continuous forms. I would expect this application to use a mainform with the vehicle details and a subform in continuous mode for the speakers. Deleting entries in a continuous form is simpley a matter of using the record selector and pressing delete. Similarly, what validation problems are you expecting?

For other components there are two ways of going. One is to have a single table for all components and a field that indentifies what they are. So a six speaker car with a head unit and two amplifiers would have 9 records.
 

Bobadopolis

No I can't fix it dammit!
Local time
Today, 09:59
Joined
Oct 6, 2005
Messages
77
Point taken about the delete thing, thanks - for some reason I had it in my head that you couldn't delete records from a continuous form.

However, from my end user's point of view and in terms of rigidity it is easier to break the car down into 'regions'. They are treated differently anyway (surround regions and centre regions would not have woofers for example) and subwoofers are a different matter altogether.

When entering the speakers using a continuous form it would be possible to input records for 100 woofers into the subform for a given region. Whilst (as you have pointed out) you can delete them it would be ideal to validate them so that no two speakers of the same type can occur in a given region. A similar problem occurs for data capability - a Data Capability table reads from a Data type table (e.g. MP3, WMA, etc.). With this setup you could input MP3 into a continuous form 1000 times! I don't know how you would prevent this... Of course you could make these fields and yes/no data instead but that's not very future proof!

The other problem with continuous forms is that, for example, if the surround speakers consisted of only a mid-range speaker then the input form and report would need to make invisible the image of the tweeter. This would require reference to a specific record in the continuous form which I believe is also virtually impossible! Unless anyone knows how....

Thanks for your response,

Bobadopolis
 

Users who are viewing this thread

Top Bottom