Normalization

After reading up on databases and how to set them up, its key to break up the database into sections. I understand that and how to make foreign keys.
I have created one database with 12 tables. Some of the tables are not connected to each other. Others I want to connect.
Right now I am working with the Company Index table that has 9 fields.
companyID,companyName,companyShortName,CompanyAddressNo,companyAddressLine1,companyAddressline2,companyCity,companyState,companyZipCode
This table is the main table and the other tables have the companyName and companyID as the foreign key.
The 2nd table is the options table. I would like to use this as a switch to turn on an icon or off. optionTwitter,optionFacebook,optionWebpage,optionRank and optionLevel.

I would like to add data in the Company Index table and have each and every table with the companyName and companyID to update.

Does this make sense and the correct way to do this?

Mike

Hi Mike,

You seem to be on the right track. The way I think this would work is like the

1. Your Company Index table is acting like a container which will hold data from the company table and the options table. e.g

  Company Index Table

companyIndexID (primary key) INT
companyID INT
optionsID INT  

2. You should then have the company table which should look something like this

  Company Table

companyID (primary key) INT
companyName VARCHAR
companyShortName VARCHAR
CompanyAddressNo VARCHAR
companyAddressLine1 VARCHAR
companyAddressline2 VARCHAR
companyCity VARCHAR
companyState VARCHAR
companyZipCode VARCHAR  

3. Next your options table can be set up like this

  options Table

optionsID (primary key)
optionsTitle VARCHAR


Your data should look something like this

ID
1. Facebook
2. Twitter
3. Webpage

etc  

If you wanted your company index table to have more that one option selected you could add

  Company Index Table

companyIndexID (primary key) INT
companyID INT
options1ID INT
options2ID INT
options3ID INT
options4ID INT

etc  

The main work comes in on DW and WA. You would run data assist wizard on your company index table. Once you have done this you would need to use MYSQL JOIN to display data from your company and options tables on your detail, results and update pages.

In relation to turning options off, when you run data assist wizard it will pick up on your optionsID foreign key in your company index table. dataassist will guide you through make a dynamic list or checkboxes to display your options.

MYSQL JOIN is the key to getting all your data from various tables to display after you have run the data assist wizard. There is also something called MRT from webassist but unfortunately I do not really know how it works.

Hope this helps a little

Thank you for the reply.

I created the 3 database tables that you suggested.

Do I have the Company Table and Options Table auto index?

I know I have to make the companyID and optionsID unique within the Company Index table so I can create the foreign keys.

For some reason I can create the companyID one, but the optionsID isn't working.

So what does MRT mean. I am trying to find it so I can look at it.

Again, thx for your help.

Mike D

Hi Mike,

Yes you would make both tables primary key Auto Incretment. I am not quite sure why the optionsID foreign key is not working. Did you manage to resolve it?

MRT refers to Manage Relational Table. This is a webassist server behaviour that heps create database relationships in dreamweaver. I have not used it before and I am trying to find out more about it. You do not really need to worry too much about that for your scenario.

Once this is resolved the next step will be to figure out whether you want a company index record created every time a new company is added or do you want to manually assign a created company record to the company index?

Thank you for taking the time to help Jenny.

I didn't get the optionsID relationship to work. I tried it manually or the graphic way in phpmyadmin.

The name is the same, type is the same. The options table optionID is primary and auto_increment. Table types are all InnoDB. It should work. I did it the graphic way (Designer) with the companyID from the Index Table to the Company Table. I will work on that later tonight.

This is how I expect or want the company index to work.
1. I want to add the basic company data and that is from the Company Table.
2. If they pay to have their company index to show if they have a twitter,facebook or a webpage link, I would like to bring up that company name and switch those variables on. The index would look like the yellow pages that shows company names, addresses and phone numbers, but I would add different links that could be available. I could switch these on in the admin pages.

Once I have this 1st step, I can work on the other relationships to other tables.

Thanks

Mike D

Hi Mike

The problem is that you are trying to establish the relationship in the database. You are not suppose to do this.

All you need to do is create the table. You establish the relationships with DW and WA data assist.

Who will add the company infromation will it be a front end user or admin?

Based on what you have described I think you will need the following.

1. You will first have to create a registration/ login using security assist.

This way if a user visits your website and decides they want to sign up to your company index they register first and can be redirected to a form so they can add their data.

In order to accomplish this you will need 2 additional tables if you do not have them - a user table and a user category table.

  sample set up

user table

userID INT PRIMARY AI
companyID INT (do not establish the relationship in the database)
userFirstname VARCHAR(50)
userLastname VARCHAR(50)
userUsername VARCHAR(50)
userPassword VARCHAR(50)
userCategoryID INT (you will need to create this table)

User category table

userCategoryID INT PRIMARY AI
userDescription VARCHAR(50)

sample data for the user category table

ID
1. user (This is a basic registered user)
2. poweruser (These are users who have paid for additional features)
3. admin ( this is you as admin)  

Based on your user category table security assist can create rules to only display certain database elements based on a users category.

If you want users to purchase you will also need ecart and universal email.

Hope this helps

Hi Jenny,

Been sick a few days and I must go to Cedar Point tomorrow.

My daughter will have me on every crazy ride. I think I will be sick again.

After a bit, I figure out the problem with the setup on the Foreign Key. I know every field must be the same. I didn't have the auto-inc setup for the companyID, but I had it for the optionsID. Changed the optionsID to nothing and setup the key. Change both companyID and optionsID to auto-inc. Relationship resolved.

I am making up the backend on this website. The company will not add their name and options. I want to add that to make sure it's a real company and not something bad like porn or other bull.

Once I have the company index complete, I can offer the companies their own page using a CMS system. I wish I could have used the webassist, but each company cannot have their own login. I found another one with PHP for $300. Looks really good.

So back to the database. I am the login set and working. Once a company is indexed, I can setup their links and how the index works.

So now I am hoping when I add the info for the company, it will setup the ID within the companyID and optionsID.

Mike D

Hi,

I can use the webassist datawizard to add, update and search within a table.

I can create a recordset to show the company name and it's associated Options.

Now I need to update this and what do I use within webassit to do that?

I can Data object/update record wizard, but having some issues that I need to understand.

I can pick the connect and the table I want to update. Recordset that I have created. Why the Unique key column that I cannot update?

Getting closer I hope.

Mike D

Are You refering to step 1 of the data assist wizard? By default you cannot update a frimary key field that auto increments. You can however easily change the data for the primary key data.

Is this what you are refering to when you say you cannot pick up the primary key?

Hi,

Nope. In the 1st step you pick the table that you want. I understand that you cannot update a primary key since each time it auto increments with a new number. That makes it unique.

Actually now I have 2 questions.

I can make recordsets simple (one table) or (two tables) complex. I created a rs that had the company name and optionsID's. have the sql below. Now I can see the company and the optionID. I need to update the options for each company. I am looking for the best way possible to do that. Would that be form assist?

The CompanyIndexTable has these fields.

1.companyindexID Primary
2.companyID Unique
3.optionsID Unique
4.optionsID2 Unique
5.optionsID3 Unique
6.optionsID4 Unique

The OptionsID is tied to the OptionsTable that has Twitter,Facebook and webpage (3 options) and I can pick the number. What is the best way to what the name that is selected and not the number?

SELECT companytable.companyName, companyindextable.optionsID, companyindextable.optionsID2, companyindextable.optionsID3, companyindextable.optionsID4
FROM companytable, companyindextable
WHERE companytable.companyID = companyindextable.companyID