Newbie, New Database

[Russ]

Good day all,

I think I might be diving straight in to the deep end here as I have never designed or built a database, and have limited to zero experience with Access.

I am looking to build a database to manage various data sets in a manufacturing environment. Things like project data, part numbering management and simple PLM (product life cycle management) such as ECNs.

I've put together a data structure as below, which will likely evolve as I begin to understand what data is needed to be controlled and what simply cant be achieved.

I've started putting together the simple tables and forms, such as Companies and Contacts, and will start to layout the more complex tables of part numbers etc later today.

Im not sure I have any specific questions yet, but some feedback on if I have built the data structure right, and if you think it will all link together ok. Also, any improvements or ideas I should try and incorporate.

I will with out any doubt follow with a raft of questions.

Many Thanks
Russ

[Bob Fitz]

Before you do anything else, I would strongly recommend you to read up on database design.
I certainly would not continue with what you have.
Avoid spaces in the names of objects (tables, fields, reports etc)
The relationships between your tables are wrong. Assuming that the "ID" fields in tables "Customers" and "Contacts" are Primary Keys, then the related tables should be related to those "ID" fields.

[Bob Fitz]

The following link has many worthwhile links: https://www.accessforums.net/showthr...133#post352133

[CJ_London]

would need to know more about what it is intended to do but some comments that may be relevant

Project/Contacts. This is a many to many relationship and would normally be handled with a joining table. What if there was an engineer 3?
Project/Parts - possibly the same here - your field name is 'associated parts' which implies more than one. So does B.o.M, ECNs, Affected Part Numbers and possibly Notes
you have a field called 'revision' - if you need to maintain a history, this needs reviewing. If it is related to CNC Programs, then you need an additional link between these tables

Not sure if it is your schematic but project should be storing customerID, not name. Same applies to project/contacts - should be storing contactID

avoid spaces in field names. More importantly, avoid non alpha numeric characters (./*)
avoid reserved words (Date)

[RayMilhon]

Read up on Normalization will save you a lot of headaches.

[mike60smart]

Hi Russ

Can you upload a zipped copy of the database?

[Russ]

Thank you for your quick replies.

Looks like I'm trying to run before I walk here, but hopefully I can make progress step by step.

I will take a look at all the reference material sent and try and work out where I am going wrong.

Attached is where I am at the moment, buts its very early stages, forms have not been setup yet, fields not been set correctly yet, etc.

[mike60smart]

Hi Russ

There are so many things wrong with your current table structures which have been covered by others in the Thread.

To give you an example of how tables should be related:-

tblCompanies
-CompanyID - PK - Autonumber (You had this as a Text DataType)
-CompanyName
-CompanyNumber
-Website
-Market

the Companies table is then I presume to be linked to tblContacts?

tblCompanyContacts
-CompanyContactID - PK - Autonumber
-CompanyID - Number - FK - (linked to the Parent PK in tblCompanies)
-Firstname
-Secondname
-Position
-Telephone
-Mobile
Email

[Russ]

Hi Mike,

Thanks for taking a look at the current layout.

I had set the Company ID to text and not an auto number as I assumed the auto number was numerically sequential only, and I had planned to make the Company ID be two characters and three digits. So AccessForum would be AC001 for example.

I will try and work on the structure better

[Bob Fitz]

.....as I assumed the auto number was numerically sequential only...

IMHO they make the ideal Primary Key

..and I had planned to make the Company ID be two characters and three digits...

I would store this data in another field.

[mike60smart]

Hi Russ

I would recommend that you just have an Autonumber Primary Key in all tables and not to use them for anything other than automatically assigning a Unique Identifier for Each Record.

[ssanfu]

When I see "ID" in a field name, it signifies, to me, that the field is numeric. I go a little further and use a suffix of "_PK" for Primary keys and "_FK" for Foreign keys.
For the Customers table (which I would rename "tblCustomers"), I would name the Primary key "CustomerID_PK" - the field type would be Autonumber.
In the Projects table (which I would rename "tblProjects"), I would name the Primary key "ProjectID_PK" - the field type would be Autonumber. The link to the table "tblCustomers" would be a Foreign key "CustomerID_FK" - the field type would be Number - Long Integer.
Table names are plural because they hold info on many "Things" (example Projects). PK fields are singular because each record is about one "Thing".

It is important (IMHO) to have a naming convention (and follow it). I've seen dBs that didn't have a naming convention and it was horrendous trying to follow the flow. If you have a table named "Project" and a query named "Project" and fields named "Project" in many tables - well, I lost a lot of hair!

I really dislike names like "Mobile No". Is there a "Mobile Yes"? Use "Num" instead of "No" - it's only 1 more letter....


You might try searching for "naming convention database".

I modified your posted db - changed object names, changed record sources (for forms).
And speaking about field names and PK/FK fields, See Microsoft Access Tables: Primary Key Tips and Techniques


I modified your dB. I did not change the design - mostly just changes names. Design still need work.....