Relationships

[torz]

Hey Guys,

I've been trying to wrap my head around how relationships work and then once they are setup how that effects the design of forms, queries etc.
I'm rather new and I know what I currently have is no where near being setup correctly (have not used any relationships etc).

I was wondering if anyone would be kind enough & have enough time to take a look at what I have so far and then help make the changes required for relationships.

I haven't posted the current DB yet as I'll need to make a copy and remove a lot of the data that is currently in there, I just wanted to make sure someone is kind & willing enough to help before I make the copy

 

[bob fitz]

Well, IMHO getting the structure of the db and the relationships right is at the heart of creating a worthwhile db.
I am no expert but would be very happy to take a look and give some advice, whatever that may be worth. I'm sure there are many others here who would be happy to help, so my first piece of advice would be to go ahead, make the copy and post it

 

[CJ_London]

I'll need to make a copy and remove a lot of the data that is currently in there

The easisest way is possibly to create a new db and just import the tables you require, setting the paramaters to structure only. Then repopulate with a few test records

 

[torz]

Thank you both!

I know there is probably a lot of work to get this to where it should be...
Any help you can provide would be amazing. with the login you can use
User: d393393
Pass: Temp123

You'll end up with errors poping up until you add details into the tblusersecurity_sec table (your login name for windows in the userID and an email address), have not finished fixing the dlookups to handle null values as it was not much of a concern in the environment it is being used.

So the main form / table will be the flsfeedback.

Any questions shoot me a PM or a post here!

Thank you again!!!

 

[tehNellie]

Oh boy.

On the plus side there is a database trying to get out here.

Lets try and start with the low hanging fruit.

1) Don't call the primary key in more than one table "ID" if it's an inorganic (auto number) key; call it tablenameID or at least something specific. When more than one column is called "ID" (or "name", whatever) you'll tie yourself up in knots trying to remember which column, in which table you're referring to. especially if its a foreign key as well. When you have a column in another table called "TablenameID" you know damn well that you need a join to another table without having to look at a document or a schema diagram.

2) tblNPS:
Breaks normalisation rules. Q1 suggests a Quarter period yet you have 8 quarter columns defined. It should be one row per quarter. TblUserPreference does similar. value1, value2, value3 etc in a column is bad news UNLESS you know what those values are at the point you create the row (and they won't change). They're great in Excel (I work in finance) but terrible in a database.

3) Column naming
DONT use spaces in column names. Use CamelCase or under_scores but not spaces, please.

4) Relationships
You've started to use them, congratulations (seriously!). Make sure you enforce referential integrity when possible. At the point you have formal relationships defined and you do what you have done and put your DB up for someone else to look at it makes a lot more sense. We might not understand exactly how it works but we can see how all the data fits together.

 

[torz]

Oh boy.

On the plus side there is a database trying to get out here.

Lets try and start with the low hanging fruit.

1) Don't call the primary key in more than one table "ID" if it's an inorganic (auto number) key; call it tablenameID or at least something specific. When more than one column is called "ID" (or "name", whatever) you'll tie yourself up in knots trying to remember which column, in which table you're referring to. especially if its a foreign key as well. When you have a column in another table called "TablenameID" you know damn well that you need a join to another table without having to look at a document or a schema diagram.

2) tblNPS:
Breaks normalisation rules. Q1 suggests a Quarter period yet you have 8 quarter columns defined. It should be one row per quarter. TblUserPreference does similar. value1, value2, value3 etc in a column is bad news UNLESS you know what those values are at the point you create the row (and they won't change). They're great in Excel (I work in finance) but terrible in a database.

3) Column naming
DONT use spaces in column names. Use CamelCase or under_scores but not spaces, please.

4) Relationships
You've started to use them, congratulations (seriously!). Make sure you enforce referential integrity when possible. At the point you have formal relationships defined and you do what you have done and put your DB up for someone else to look at it makes a lot more sense. We might not understand exactly how it works but we can see how all the data fits together.

Thanks for the info, @thenellie! I found the old one that I had started before but needed something working.... there is a whole bunch of relationships in here *BUT* they must be wrong, *OR* I just have no clue what I'm doing. It's probably a bit from column A and a bit from column B

I can never put stuff in some of the tables because it wants data in the related tables... I guess one of my biggest issues with the whole relationship side of things is, once they are there, how the hell do I build the from to get the data I need for each table?

Here is another copy of what it started like before I got told it needs to be used NOW :banghead:

 

[torz]

I guess to give a bit more of an idea how the whole thing should work ideally....

Call centre environment


1. Customer calls
2. Consultant has no idea & calls a support team
3. Support team capture details of call to analyse to find trends for upskill opportunities / process break downs etc
4. If unable to resolve on the call it will generally be for 3 reasons which are the itam, pet and followup tables.
5. if itam has to be raised details captured & followed up by the assigned support agent
6. if pet raised - same as above (#5)
7. if followup raised - same as above (#5 & #6)

8. both the pet & itam could require followup work once resolved so the follow up work for that is captured in the followup table.


Thats the general gist of it

 

[Thales750]

Back to your original "Relationship" question. it's simple...

Bring her flowers often, and just say yes dear.

It works for me, plus I would use the naming format:

tblUpperCaseLowerCase

and leave out the dashes and the underscores. Its easy to read, and anyone doing professional programming will instantly recognize it.

Just

 

[Thales750]

I do have a suggestion though, try to find a sample database, or use a built in wizard ,that is similar to the one you are trying to create, and reverse engineer it.


Do a google search on Bob Larson, and Allen Brown, they have web pages full of beginner info.

 

[torz]

Thanks for the suggestions @Thales750

I've been back to Allen's page a number of times, and I'm sure i've seen bob's a few times as well haha. I just have not had enough spare time to be able to sit down and have a proper read & then play around with things. So yes I know ultimately its my fault how bad things are and I know that they are never going to get better unless I do make time for that (which I hope to in the near future) I guess I was more so hoping that someone may be able to take a look @ the 2nd one and let me know if I was on track or if it was WAAAY off. To be honest, I have no idea how long it would take for someone to do that, being that I have no idea & haven't grasped the concept yet... Hopefully I can manage to book some annual leave @ work and can put some real time into learning things properly.


Thanks for everyone's time thus far & the feedback!

 

[gemma-the-husky]

a relationship does not directly affect the database/form design. all it does it asserts various properties about the records in the tables.

so if you have customers and orders, with a 1-many relation - it prevents you putting an order on for a non-existent customer. Which makes sense. The system would be designed to set up the customer first.

 

[torz]

Hey Gemma, thanks for the reply, I do kind of get the concept behind it, I think I just need to see someone build a basic one that I can relate to a work situation and get them to explain why they chose that way (not in great detail or anything) if that happens I'll be fine from there I think. My brain learns in weird ways sometimes

Might try and grab one of the guys from work when it's not so busy for an hour or two, then I can stop wasting everyone's time here

Thanks again to everyone that has posted, you have a helped a little bit here and there!!

 

[jdraw]

torz,

Here is a tutorial re database design that deals with tables and relationships.
The author starts with a business description and leads you through the creation of tables and relationships.

 

[torz]

Thanks jdraw!

was helpful for what I have seen so far, just been so busy lately and had not had much of a chance to have a good look or continue with the project at the moment, hope to get back into it soon!!

 

[Thales750]

Unfortunately I have many other thing to do besides create software. So I pick a time, and everyday, no matter what, I write.

After a while things start to show up.

 

[RainLover]

Thank you both!

I know there is probably a lot of work to get this to where it should be...
Any help you can provide would be amazing. with the login you can use
User: d393393
Pass: Temp123

You'll end up with errors poping up until you add details into the tblusersecurity_sec table (your login name for windows in the userID and an email address), have not finished fixing the dlookups to handle null values as it was not much of a concern in the environment it is being used.

So the main form / table will be the flsfeedback.

Any questions shoot me a PM or a post here!

Thank you again!!!

You don't give a clue or hint as to what version of Access you are using.

Next time could you convert to 2003 before uploading.

 

[torz]

Hey Rain,

Sorry I didnt mention it in the post itself, the tag has Access 2010

And for sure I could convert to 2003, does everyone generally prefer it to be in 2003??
I'm rather new to the forum so not really sure what the best practices are sorry

 

[torz]

if you were interested in taking a look and giving some guidance/assistance, I'm more than happy to convert to 2003 for you

 

[Thales750]

Hey Rain,

Sorry I didnt mention it in the post itself, the tag has Access 2010

And for sure I could convert to 2003, does everyone generally prefer it to be in 2003??
I'm rather new to the forum so not really sure what the best practices are sorry

The older the version, the more people can open it. but as you go backwards in versions you lose functions.

 

[torz]

The older the version, the more people can open it. but as you go backwards in versions you lose functions.

I guess it didn't really matter in this instance how old it was since it was just to do with relationships and stuff not really code or features?