Why am I getting two PF & not one PF or FK using DeZign

[moto485]

Hi I am still learning this ERD stuff and am trying out DeZign for a MSSQL database and I thought I had PK and FK all figured out now im am reading about PF and trying to understand.

Reading about identifying I thought the CL_Address table should just have the "PK clAddressID" and "PF clClientID" as the Primary Key. Why is it adding the other PF key ??

EDIT: okay so I just realised in the CL_Client table the key is a combination of PK and PF I guess that is why.

So why bother with identifying relationship the non-identifying relationship seems do the same thing ???

 

[RainLover]

It may be possible for a Client to have more than one address, and it is also possible for more than one client to share the same address.

This is known as a One to Many relationship. In order to sort this type of thing properly it would be better in my mind to design these myself rather than have someone else's work tool do it for you.

I will attach a document on Naming Conventions as I think you need something better.

I hope this is of some help.

 

[moto485]

Thanks, I think i need to do a lot more reading on MSSQL databases..

Regarding the naming conventions because I am going to be building a much larger system I did the following in the ERD.... Employee database would have "em" appended to the table and attributes that way emAddress (employee) and coAddress (company) side by side or teAssetID (testing) and asAssetID (asset) it is clear to me.. is this not a good way of going about it?

 

[RainLover]

Moto

How big do you think this is going to be.
I would have thought a Database of a size that is too big for Access, may be too big for a person with limited experience to tackle. I could be wrong. I hope we can get opinions from others.

How is that plan going.

 

[moto485]

well it is going to get quite big but will start small and workup over this year, so Access will okay at the very start but will out grow and security will be better with mssql plus I can create apps in php for guys on the road.. my database skills are not up with my php but you gotta start somewhere

 

[RainLover]

If you are going web style with PHP and MSSQL then that is a good approach for an internal product. For something outside of your own environment I would suggest MySQL.

You still need a naming convention. The one I gave you would be suitable or you could look further a field.

Also if you read the document I posted it will help you to understand Primary Keys etc. Access has some protection features that either SQL does not have.

Finally you could do some basic design work in Access then when that is correct you could upsize to SQL. I am limiting the basic design to the Tables not Forms Reports etc.

 

[moto485]

I would go with MySQL (which I already know) and Access but I thought they might not play together as well as MSSQL but if you have used MySQL with Access and had no problems then I might change my mind and go with that?? I have tried myself and it seemed to work well but haven't put it into practice on any jobs..

I looked at you word doc before and most of your naming conventions I am doing with one or two slight differences but relatively the same.

Regarding the relationship stuff I understand the PK and FK it is just when I started seeing PF keys I started to get lost :banghead:

Yea I am thinking along the same lines with the tables get that in order and then move to mssql or mysql and then start with the vba

 

[RainLover]

I don't know what a "PF" key is. Could you explain.

SQL Server costs money where as MySQL is 100% free.

If you were to look at the servers all over the world that host Web pages I would guess that about 80% or more are MySQL. You would need to check this out some how if you really wanted to know.

PHP works comfortably with MySQL.

Access also works comfortably with MySQL. There are some differences but you will pick them up quickly.

Hope this helps a little more.

 

[moto485]

The Primary Foreign Key (PF) are foreign keys made part of a composite primary key. I thought PF was something unique to MSSQL but it turned out learning about it was a real waste of my time :banghead:

Thanks for you help I will go with MySQL since I already know that

Thanks.

 

[RainLover]

The Primary Foreign Key (PF) are foreign keys made part of a composite primary key. I thought PF was something unique to MSSQL but it turned out learning about it was a real waste of my time :banghead:

Thanks.

I don't think that that is right. But then lots of people have different ideas and have learnt on different engines.

My rule which is often challenged by some people who cannot or will not try to understand the concept.

The Primary key is always AutoNumber. Therefore it cannot be duplicated. It only requires the one field. It is never seen or used by the end user.

A Foreign key is also never seen by the end user. It is a Long Integer. More than one can be used in one table.

A Composite Primary Key (The combination of two or more fields) should never be used.

If you run into a design that does not meet my criteria let me know and I will explain more. Simply create a new post then send me a PM letting me know that it is there.

 

[RainLover]

BTW Have you realised that the use of third party tools is not always the way to go.

As you have already pointed out DeZign has caused you problems. You will one day realise that you are smarter than this tool.