I am working in a MySQL database that will allow various users to make entries of properties, in my inexperiance I am wondering if it is best to have a separate table for each user "users properties" or just one universal table for all.
Are there any pros and cons to these options?
The values entered will all consist of the same values, so if separate tables where used they would all contain the same column names. Entries might reach somewhere in the 1,500,000 region.
I am puzzeled as to the efficiency of the database when or if the tables "not entries" reached 40 or 50 or even 100 in comparison to just having one table for all users entries. The problems I can image with having one table rather than seperate ones is if any of the users requested a dump of their entries, would this be as simple if they all had a seperate table? Performing a search on a table with 1,500,000 entries might be considerably slower than one on a table that has 1000 entries.
Thanks for anyone advice on this.
MySQL, best to use various tables or just one?
Moderator: General Moderators
why not have a users table and a settings table.
Then link the two using uniqe ID's.
have a look at PHPBuilder.com - Database Normalization And Design Techniques
Mark[/url]
Then link the two using uniqe ID's.
have a look at PHPBuilder.com - Database Normalization And Design Techniques
Mark[/url]
That's what I do or will have, the questions I have are relating to a separate table for each members entries or a universal table for all members entries. Maybe I am worrying about something I don't need to worry about and i should just use a universal table for all entries although it would be nice to know an experianced MySQL users feelings on the questions.
Thanks
Thanks
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
Depends upon what you mean by properties.
Tell us what info you are wanting to store for these Users. Cannot make a blanket statement about store all info in 1 table or N tables without knowing the nature of the data and how it might lend itself to some normalization, if needed.
i.e. if you were storing some phone numbers:
home phone
work phone
it might make sense to split that out into another table and easier to add cell phone and fax, etc. at a later date. Keep in mind 'what-ifs' and future uses/expansion of the data you wish to capture.
What is the data?
fv
Tell us what info you are wanting to store for these Users. Cannot make a blanket statement about store all info in 1 table or N tables without knowing the nature of the data and how it might lend itself to some normalization, if needed.
i.e. if you were storing some phone numbers:
home phone
work phone
it might make sense to split that out into another table and easier to add cell phone and fax, etc. at a later date. Keep in mind 'what-ifs' and future uses/expansion of the data you wish to capture.
What is the data?
fv
The databse is for anyone who has homes "properties" for sale to place them into a database, the minimum number of properties "Homes" that a user will have is about 100 and could have up to 2000 or more, there could be between 1 and 500 users.
There will be images but these will be uploaded to a folder with the names only being stored in the homes "properties" table. The properties table will have about 31 columns (21 varchar, 6 int, 3 tinyint, 1 text) and these will contain basic information regaring the property or home that is listed, bedrooms, bathrooms, town, country, description etc.
The entries that are placed by a user will be returned using their search page, but could also be searched from another users search page if both members aggree to this.
My worries are if there will be a slowdown on having all the homes "properties" listed in the same table and would it be faster or better to create a table for each user consisting of that users properties only, if in the latter case and some of the members had aggreed to have their entries searched on other members searches, would the join then create a slowdown greater than having all the entries listed in the one table, is there a limit to the number of tables that can be joined?.
The member may request txt a dump of their entries, is this possible to seperate the member entries in a table used for all the members home entries, I am using myphpadmin at the moment and don't see an option other than a dump for all entries.
Many thanks
There will be images but these will be uploaded to a folder with the names only being stored in the homes "properties" table. The properties table will have about 31 columns (21 varchar, 6 int, 3 tinyint, 1 text) and these will contain basic information regaring the property or home that is listed, bedrooms, bathrooms, town, country, description etc.
The entries that are placed by a user will be returned using their search page, but could also be searched from another users search page if both members aggree to this.
My worries are if there will be a slowdown on having all the homes "properties" listed in the same table and would it be faster or better to create a table for each user consisting of that users properties only, if in the latter case and some of the members had aggreed to have their entries searched on other members searches, would the join then create a slowdown greater than having all the entries listed in the one table, is there a limit to the number of tables that can be joined?.
The member may request txt a dump of their entries, is this possible to seperate the member entries in a table used for all the members home entries, I am using myphpadmin at the moment and don't see an option other than a dump for all entries.
Many thanks
Just personal thoughts...
You should not create a table for each use as this will get ugly to maintain in the end. A few big tables are much better that alot of joins' trying to retrieve any data you want imho.
You can dump information, but for selective data, use a query to fetch the data you want, and simply print it to a file/screen using the filesystem functions (fwrite() as example). This was you have abit more control of the dump-layout (if wanted).
You should not create a table for each use as this will get ugly to maintain in the end. A few big tables are much better that alot of joins' trying to retrieve any data you want imho.
You can dump information, but for selective data, use a query to fetch the data you want, and simply print it to a file/screen using the filesystem functions (fwrite() as example). This was you have abit more control of the dump-layout (if wanted).