Am I wrong?

adrianuk29

I am quite concerned about my table structure, my database is filling up fast and I have a few concerns about the structure of my tables.

I have 5 tables in my database and they are all related to each other. On some occasions it is necessary to search across 3 tables and display the results. I have a primary key (id) on each table which I now think is wrong.

Should I not have 1 primary key only, and the rest as foreign keys. When I tried to run a simple query the other day it did not work and I am wondering if it is becuase of my poor table structure. I ended up producing a very poor work around, which is laughable but does the job. The simple query I tried is below and it caused an error and I am thinking it is becuase of my structure and the use of primary keys.

For example blah blah
select * from table1,table2 where table1.id=table2.id AND id=$id

Will having a poor table structure cause me problems later on and lead to errors?

What is maximum file size of a table in mysql, my hosts use linux redhat I know that their is a difference between os?

The more I look at the tables the more I think, there was no need for that, or for this as it is already covered in the other tables, for example I posted the date across four tables, that was before I learnt how to search two tables so this will definetely be removed as the date is only needed on one table. I dont mind admitting to my bad mistakes, its the only way I learn. I also posted a user password on two tables which again is not needed once I had learnt about searching two tables. lol.

Your help would be greatly appreciated. I dont wont to look at the website one day and find that mysql has gone tits up.

tekky

you would probly wont to do something like

select * from table1,table2 where table1.id=$id and table1.id = table2.id

or...

SELECT * from table1 LEFT JOIN tabl2 ON table1.id WHERE table1.id = table2.id

(I'm not the best with joins... but I'm thinking that would produce very similiar results....)

leatherback

Hi,

In general:
- Each table should have a primary key. For that table!
- It doesn't have to be bad when you have to search across 3 tables to get your results out. Actually.. Sometime sit is the best solution.

What you really have to be concerned about, is the duplicate storage of information. Not just because it take sspace. More importantly: When you need to update information, you will have to update this in several tables!

This is also where my point of searching multiple tables comes from:
Say, I have a database with pictures, and a lot of keywords for the pictures. Now each picture can have several keywords. I could of course create 3 colums for each picture, which could hold a keyword. But that would result in huge databases, and difficult searching. Then I could create a new record for each keyword. But that would lead to a lot of duplicate records for the image. What you would do, is create a table with keywords, each keyword occurs once, and has it's own ID. Then you would create a table, where a link between the picture ID and the keyword ID is created:
T_ID | P_Nr | K_Nr
( Table record ID, Picture Number, Keyword number)

Then you can seachr for pictures using this linking table, and neve rhave duplicate information.

Hope this helps a bit?

adrianuk29

Thank you for all your help. It is always good to get a second opinion from the proffesional on these matters. I know for a fact I can certainly streamline my database for the future. When I first started my database I chucked everything in one table which is really bad practice. Becuase of the seperate tables I now find the data easier to manage and easier to pin point problems.