Absolute basics of database design

[thegrimmerdiscovery]

Hi people



I got some invaluable support here last week and am hoping for some more in terms of d/b design fundamentals.

I've been putting in some spadework in terms of searching around online, starting up a MOS Access course and ploughing through a Dummies guide to d/b design but none of it so far is offering up the guidance I'm asking for here. Or maybe it is and I'm just not getting it.

My aim
To create a database to help manage and provide reporting functionality for projects that start and end throughout the year.
So far it contains just two tables.
They are deliberately linked as 1 to 1 relationship.
Table A contains fields that are project identifiers such as Project Name, Our Project Ref, Project Owner, Project Workstream.
Table B contains fields such as Project Start Date, Project End Date and several long text updates fields.

My issue
This probably reads as hugely flawed in terms of design and I'm aware (at least I think I am) that this could be condensed into just one table and I could do away with the relational aspect and save me lots of time and mental strain up front...but I'm really eager to get a grasp of this whole learning process. I can feel the gravitational pull of Excel grabbing hold of me but I'm resisting returning to what I know. However, my goal is to keep the contents of Table A and Table B separated (for various reasons) albeit linked with the Project Name and Our Project Ref fields - and by this I mean I need both those fields to appear in both tables. I'm aware that this data duplication is not considered good practice.

Finally, my question
What is the best way to go about this bearing in mind I really do what Project Name and Project Ref to be available in both tables?

And thanks in advance for your patience and knowledge and time.

[ranman256]

Why is tProject in 2 tables? Make it one.

It may need a sub table,tTasks. 1 to many, w tProject as master table.
projID,TaskName,TaskDescr,etc.

[orange]

Welcome to the forum.
I am posting a link to many articles on Database Planning and Design etc. The tutorials from RogersAccessLibrary with these materials are a proven way to experience database design. You will have to work through a few tutorials to appreciate the process. Each will lead you through a clear description of a proposed "business" and teach you how to identify the business facts and convert these into a design of tables and relationships. Working through each tutorial will take about 45 minutes to 1 hour. You have to work through the tutorial to get the experience.

Also in the linked materials are several articles on related topics including Normalization, data modelling, test scenarios.

Database is not spreadsheet - any many have stated that they must "unlearn" their spreadsheet approach in order to work with database. Access and Excel are built on different object models. While some things "look similar", and some things are similar (Vba language), these are separate products.

Rarely are tables in 1 to 1 relationship--they can be for certain purposes, but tables in 1 to 1 relationship can be collapsed to a single table.

Work through a few tutorials; review some of the reference videos. Then using your own "proposed database requirement" create a data model (pencil and paper); create some test data and scenarios; play stump the model (mentioned in the link).. a get a"tested model" to serve as a blueprint for your database design.

Do not be too quick to jump into physical database.

Good luck with your project.

[thegrimmerdiscovery]

Thanks so much, Orange - I'm in the process of working through your advice, the links and the attachments. Really appreciate your time.