Is my design ok?

[IanMilly]

Hi

I am putting together a dB which will be used in logging equipment to be sent out to customers using a barcode system .

I have so far made a few tables so far which are linked in a one-to-many format.

There will be further tables and fields to be placed in these tables (i don't want to do loads and find out it is all wrong - again!!)

I will create a Serialnumber in a separate dB (or unrelated tables). The tables shown here are an example of when the barcode/serialnumber is read into the system. I want to be able to record the serial number against a "Panel" (cabinet), which is under a certain project number which has been assigned to a customer. All settings of the individually serial coded cards shall be recorded in the in TblCardSettings.
The serial numbers will have been previously created and the value of the serial code will be either typed or scanned in (using a barcode).

I have made forms which are linked to the tables directly - is this a mistake?
If I wanted to use the serialnumber to find the customer Id (for instance), how could i do htis - i can do this for linked tables using the unmatched query - is it possible to search through a value in a field at one end of the design in order to retrieve the original table (ie using Settings (tblCardsetting) to find out the contactname (tblCustomer).

Is my design on the right tracks? If queries are needed could you pls explain how to impliment the design.

Would be grateful for any help

Thanks

Ian


(see attachment for Relationship of tables)

 

[Mile-O]

I'd add a table for cities and a table for regions.

tblRegions
RegionID - autonumber PK
Region


tblCities
CityID - autoNumber PK
City
RegionID

I'd remove region from you customer table and change City to CityID and its type to a Number.

Relate these new tables accordingly.

I also see CardType duplicated in two tables - why is this? It shouldn't be.

 

[IanMilly]

Thanks for the region advice - i will impliment that.

The cardtype in two tables was a mistake - it should only be in Cardsettings table.

Can you tell me how i can later query the system/db to find (for instance) in which ProjectNumber certain Settings are used?

Thanks

Ian

 

[Mile-O]

That's a good point. What's the Settings field all about? It's plural so I'm guessing the table may be wrong here too.

 

[IanMilly]

I am wanting to link further tables to the card setting, such as test data (of which the card settuings appears on the test form). The testing depends upon the type of card and forms are to be used to enter the data. I am trying to think of a good method to link these tests forms into this db and use the serialnumber as a reference. I may scrap the cardsettings table and opt for lots of individual test tables.

How would i be able to gather the information further back than the linked tabels?

Thanks

Ian

 

[Mile-O]

I may scrap the cardsettings table and opt for lots of individual test tables.

That's a NO-NO! (Just look here to see everyone but one rally against this type of working )

If you can describe what these tables are meant to be (as I don't know your thinking) then I'm sure, once awarded clarity, an actual suggestion may be forthcoming.

 

[IanMilly]

ok,

the cardsettings field was intended as a place to record certain jumper settings on electrical cards. It was mostly used as an end point for the sample db i posted.

With there being several types of cards this will be impossible to have in one table. There are multiple tests which are carried out on certain electronic cards. I want to create several forms for the user to "tick" boxes on completing test requirements (or enter comments where needed). tables will be related to these forms and results stored in tables. I want all the test results be stored against the serial number.

When I say lots of tables, i mean approximately 6.

A user of the system will scan a barcode from a card into the system and then enter data about the card and apply testing to it.

Later on i may be given a serial number by a customer or colleague, i would like to be able fully search when the item was tested,who tested the item and tests applied to it.

I hope that is clear now

thanks

Ian

 

[Mile-O]

In one of my databases I had to allow a user to define a questionset themselves where the answer was Yes, No or N/A and they could mix and match from any number questions and could add their own too.

I made a table for questions, a table for answers, and a table for the category (in this case the different cards).

It involved many-to-many relationships and involved a bit of programming. However, that was just my take on it. There have been some great example database structures posted in the past on survey databases which, essentially, what you are wanting for this section of your database.

Have you tried the Search function for keywords such as questionairre, survey, etc.?

 

[Pat Hartman]

With there being several types of cards this will be impossible to have in one table

- you are thinking with a spreadsheet mentality. Databases work differently. Do some reading on normalization and as Mile said, take a look at the survey posts here.