Please Help with Database design (1 Viewer)

[w.key1980]

Hello, this is my first post. I am trying to build an access database and I have got so far. But I have reached a dead end and don't know how to fix the issue. Here is the list of tables I currently have. I think I have completed the normalization process. BUT not sure.

tbl_CustomerDetails
StudentID - PK
Forname
Surname
Address
Town
Postcode
Country
Email
Daytime Number
Evening Number
DOB

tbl_CourseDetails
CourseID - PK
CourseName

tbl_CustomerResults
ResultID - PK
ResultOptions
AddedToRYA
AddedBy
FollowUpEmail

tbl_InstructorDetails
InstructorID - PK
InstructorForename
InstructorSurname

tbl_SailPassage
SailPassageID - PK
StartDate
EndDate
NoDays
AreaSailed
NightHours
DistanceInNM

The second issue is the relationships and I can't seam to work out how to do it. This is as far as i have got. By the way, i am new to access and only bee using the program a month or so. Please can anyone help / advise on this issue.

 

[Galaxiom]

Relationships are on fields that match up between the two tables.

For example, in a CustomerResults table would be expected to have a foreign key field that held the CustomerID (the PK from the Customer table) to indicate the customer whose result is being recorded. These two fields are in a relationship.

 

[informer]

Hi, w.key1980

For modelling correctly a db you must :

1. Build a conceptual data model (CDM). The Logical data model is generated from it by applying rules on cardinalities. And with dedicated tools, the LDM is automatically built as well as the SQL script for creating the DB (See files attached)
2. Use dedicated tools which build CDM not only LDM like :
   1. Power*AMC (excellent but expensive)
   2. JMERISE, excellent freeware here

And of course apply at least the 3 first Normal forms (NF).
​

 

[jdraw]

Does JMerise have an English interface?
There is also a free community edition of CA ER/Win.

 

[Galaxiom]

For modelling correctly a db you must ...... Use dedicated tools which build CDM not only LDM like :

1. Power*AMC (excellent but expensive)
2. JMERISE, excellent freeware

Must? That does seem a little strong. Many fine databases have been built without relying any modelling software.

Still, I guess it might be useful for those who are struggling to get their head around designing the model.

 

[jdraw]

I agree with Galaxiom. Must is too strong in my view. I encourage people to do some data modelling, and even point them to tutorials from Rogers Access Library to build/design their database based on the business rules.
Too many newbies think Access is going to do something magical. It only does what you tell it, and you better have a good design before jumping in too deeply.

 

[The_Doc_Man]

Informer, in re the design process and using fancy design tools:

All new Access users must remember that Access is a TOOL, not a finished product. Tools can be used or misused and useful or useless. This site's veterans know that, whether consciously or instinctively.

When I teach new users about Access, I pull out the old power tool that rotates when you press the trigger. It only does its work according to the bit loaded to it's chuck. (If you loaded a drill bit, it would be a power drill, but of course it does more than that.) Access is like the tool that has a lot of loadable parts, but ...

a. The tool doesn't make the furniture; the craftsperson does that.

b. Unless the craftsperson has done the same item a hundred times, that person will need a design document (a.k.a. blueprint or construction plan) as guidance.

c. When it is over, you don't refer to the furniture as a "Black and Decker" table or a "Skil" dresser or a "Stihl" chair. The only reason you refer to something as an Access database is if you still need to have the Access Front End or Back End active when you finish your design and implementation and actually start using that beast.

To say that you MUST use a particular tool in a project for design purposes is a narrow view of operation. First things first - in the real world, you have numerous different sources of design documents, some of which don't use any automated tools at all. Also, using a design tool guarantees nothing if you still don't have a clear view of the problem before you start using that tool. Just like any other craftsperson, if you haven't had the "shop class" on that tool, you still might not know how to use it correctly. If you've never seen a Victorian-era Armoire, you still don't know how to craft the plans for one even with the best computer-aided drafting tool in existence.

Yes, you do a LOT better with a design document that answers all of the questions. No, you don't NEED software to draw pretty pictures and enumerate a thousand different data fields. Most projects start with a basic scaffold and slowly grow from a tiny framework to a magnificent edifice over time. Just like Professor Tolkein's great stories, a database is OFTEN like a "tale that grows in the telling."

Informer, using imperatives isn't always wrong. I use them now and then. But just remember when answering a question, you can scare off someone inadvertently by making your suggestions a little stronger than they needed to be and thus making the project facing a new user a lot more daunting than it needs to be. I'm not saying you are flat out wrong. I hope to suggest that you might be more aware of user trepidation when they come here looking for answers and find some strongly-worded responses.

The only REAL absolute in all of this is that the database designer MUST know the problem before trying to solve the problem. If you are to impose an absolute on someone, the idea of "knowing where you are going" is a sine qua non issue. If you don't know where you are going, you aren't going to get there - whether you are driving a Cadillac or a Chevy or a Cooper Mini or a tricycle.

 

[informer]

Does JMerise have an English interface?
There is also a free community edition of CA ER/Win.

Hi Jdraw
You can contact JMerise developer on Facebook here

I visited the CA ER/Win website and the modelling software does't propose to build a coceptual data model
And for me it's eliminatory because build directly a logical data

• Doesn't allow to well understand fonctionnally your processes because you cannot design relations between entities with cardinalities
• And therefore it is error prone