Database design help for Photography website







class(class_id*,class_name (e.g. 'Panoramics','Square','Portrait','Custom1'), class_description)


* = (component of) PRIMARY KEY

Now you can relate images and formats to price or price tier, either as additions or multiplications of that price. You might want some tables like this:


format_price(format_id,price_id) , **Not to reply to my own post, but I wanted someone to know I made another comment.**

Posted On: Monday 29th of October 2012 06:40:03 AM Total Views:  569
View Complete with Replies

Related Messages:

MySql implementing Multiple databases or tablespaces like Oracle for performance   (260 Views)
I am in the process of designing a Database structure for a project. And i am stuck at a particular design decision. Ive searched quite a lot on google but not able to find a satisfactory answer. I am looking out for something like TableSpaces (like in Oracle) to use in MySql. I have provided a sample table structure below for better understanding of what i want to do. Table Structure CommonRepository  (This table contains only a list of Items and their descriptions) o ItemId, name, picture, description, dimension1, dimension2, dimension3, dimension4. Users  (This table contains list of users with their details) o UserId, FirstName, LastName, Address. UserItems  (This links each User to his/her list of Items) o UserId, ItemId UsersRepository (this is a Joined table/view of CommonRepository & UserItems hence will show Items only for 1 specific user) Ideally i would like to have 1 tablespace(oracle style) for each user. tableSpace types are given below AdminTableSpace  Contains the below tables in it o CommonRepository o Users o UserItems UserTableSpaces  There are many such tableSpaces, one for each user o UserRepository (This is a table if it is used in tableSpaces else if no tableSpaces are used then this is a View) Records from one user is independent of another user. Except when admin wants to generate reports of all users performance. This would be very frequent. Please note that i am expecting about 100 to 1000 users (not less) and atleast 1000 to 10,000 Items per user. Each user will have different accounts and their Items are no way related to other users Items. Hence when each user is searching for his Items for modifications, If he is querying a Table with only his data it would be much faster. This can be implemented in Oracle with tableSpaces (As far as i know, correct me if in wrong). But from what ive read. MySql Does not have TableSpaces. So thought of using 1 database for each user. Now considering that there might 100 or 10000 of users making a database for each user does not seem practical. Also many online servers may not provide so many MySqlDatabases to a single account. What design structure should i use, i have enlisted a few that i can think of, please suggest what would be the best method to implement my design, also please explain where im going wrong in my design. 1. Have 1 database, all tables in one database and use views for each user. (this might slow things down drastically for every user) 2. Have 1 database but multiple tables for each user. (is this a bad design ) 3. Have multiple databases 1 for each user and use data by using DatabaseName.TableName (This does not seem very practical if number of users are large, also ive read that some joins and queries may not work correctly for this method). Please suggest what approach i could use.
Combining records from development and production databases   (348 Views)
In my development database for a few tables I added some data. I realize I can dump the development database to the production server, but I want to leave the existing production server data intact. Can I somehow append the data of development database to the production database so that whatever changes are done by the users in the production database are present as well as changes I did in the development database are also there Perhaps, an example can clarify what I need. I have a development database called "dev" which has a table called "user". I added two new user records to it. I also changed a existing user record's department number. Production database(we can call it "prod") does not have these two users and change of existing user record department number, but it has 10 new users added to it which the "dev" database does not have. Can I ensure that changes done in "prod" are also there and changes done in "dev" are also present So, the final database in "prod" contains the two users I added in "dev", change of existing user record department number and the 10 new users which are already there in "prod" If a user with userid 1 is in department 200 in "prod" and department 300 in "dev" then the final "prod" needs the user to be with department 300. So, if there is an existing record in "prod" it needs to be updated with "dev" data. I know that I can do an insert on tables in the "prod" where additions have been done in "dev", but there are lot of tables. And, manually doing the individual changes so that "prod" table records are updated with "dev" table records would be tedious. 1. Can what I require be done If so, how Any suggestions would be appreciated.
copy specific row in the database to another 1   (243 Views)
Hi: I am not sure if i ask this correctly or my idea workable. Here is my question. I have a database that host a "user login data," and i have built a new script to expanse the service in other computer's database. I want to know are there any methods to copy the user data row from the "user login data" to the new computer's database that i have just built I am not talking doing this manually by hand, but doing this like replication a specific of row. Sorry if my explanation is not clear.
Searching my database google style   (134 Views)
I'm not sure where to start. I have been using phpadmin to search my database but I find it rather difficult to use. I would like to create a google like interface and have the rows returned were search keywords are in the row. I found a good tutorial but it is more for indexing websites. Please help me get started in the right direction. I would like to use php and mysql for the solution.
Combine data from multiple databases   (154 Views)
, I hope I can explain this well. The company finally took the plunge and we are going centralized with our database. Currently each server has a db and then has the same table on each server. The database is question has a table named users. Here is the create table syntax: Code: CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment COMMENT 'auto incrmenting user id', `login` varchar(30) NOT NULL COMMENT 'username', `password` varchar(64) NOT NULL, `name` varchar(128) NOT NULL, `email` varchar(128) NOT NULL, `phone` varchar(16) NOT NULL, `role_id` int(11) NOT NULL, `session` varchar(128) default NULL, `ip` varchar(16) default NULL, `active` tinyint(4) NOT NULL default '1', PRIMARY KEY (`id`), KEY `login` (`login`) ) Here in lies the problem: these tables were supposed to be kept the same but I know they are not. Also, if a user's password changed, it might have only been changed in one table, etc. Yes, completely ugly. Anywho, my question is, what is the best way I can go about trying to get the best and most complete results from these three tables into one. I have already told the higher ups there will be some problems but now I just want a way to merge that will cause the least problems. Thank you for your time and help, cranium
Learning proper database design and using ERD for it ?   (201 Views)
Iam a largely a self taught coder and most of my methods are probably not good. For example, if I want to build an application, Iwould start creating a database right away using SQL. Due to deficiency of knowledge and lack of planning, my databases are not properly normalized (I believe). Now iam beginning to see the light, perhaps I should draw an entity relationship diagram before actually creating a database. Please suggest me how I can Improve on this area.
Up-dating forum causes database issues   (291 Views)
I currently run a forum for gaming but the PHPbb and the portal are well out of date. I have tried numurous different ways of up-grading them but everytime I do I get Mysql database errors. My question is this..... Is it possible to create a fresh install of my forum with a new database using the same database name and password and then insert the old user data and posts data into it
Converting files to database   (240 Views)
I had posted sometime earlier in this forum but I had miscalculated my problem by then. What I am trying to do now is add a bunch of files from a folder into MYSQL database. The files are all comma separated (CSV's). Name of the files are in this format : filename.general (eg: xyz_1.general) I used 'Load data infile' command initially and it seemed to work. I wrote a perl script to automate this process but I am getting an error after a few files are processed.(only 200 files out of 516 are getting inserted) Taking the file name format into consideration i am not sure if i can use 'mysqlimport' command. files need to go into a single table. They have the same structure. Is there a limit to the number of files that can be inserted using load file Is it suitable to do it for multiple files Is there a better way to do what I am doing Pleae help me with this ! Thank you in advance!
Which databases (besides Mysql) will allow me to enter an integer as a string?   (184 Views)
, In my system I am generating queries from objects using PHP's refelection API. The 'problem' I am facing is that in PHP I have no way of telling what data type a function will return. Using mysql this is no problem, because it apparantly allows me to insert 'string' values into an integer field. That is, I can use '1' (with quotes) and insert that into an integer field an mysql won't complain and just insert 1. So I can just treat everything I find in the object as a string and mysql will figure out what to do. I don't want to tie the system to mysql. So I'd like to know if this will this also work in Oracle, Postgres, Microsoft SQL Server, etc If not, I will have to drop the whole reflection adventure I was in, so I'm hoping you have good news for me. , As far as I know most of the larger DBMS support implicit conversion between strings and numerical values. But nevertheless I suggest that you take a look at using prepared statements. When you use prepared statements you don't have to quote 'strings'. You just replace all your "in" data to the query with question marks regardless of if it is a int or a string: Code: SELECT * FROM whatever WHERE stringValue = AND someIntValue = And then you let the DBMS logic handle the possible conversion if needed. Since if the variable that you are sending in is already a numeric value and it is a numeric value that is needed in the query then no conversion takes place, while if the variable is a string then it will be converted automatically. Saves you a lot of headache and gives you better performance if you have repeating queries.
Error inserting email address into database   (261 Views)
I am inserting user login information into a database (login_id, password, email) When I insert the information, a MySQL error is returned: User registration has failed - 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 No matter what email address I try to insert, it will display the second half of the email address (, in the error. PHP Code: $login_id=$_POST['login_id']; $password2=$_POST['password2']; $email=$_POST['email']; $query1="INSERTINTOlogin(login_id,password,email)VALUES(".$login_id.",".$password2.",".$email.")"; $result=mysql_query($query1)ordie("Userregistrationhasfailed-".mysql_errno().":". mysql_error()); I've tried removing the ". & ." from around the variables in the query, as well as trying "text" and "varchar" field types.
Connect MYSQL database from outside network.   (384 Views)
Hi.. I have downloaded Mysql .Net connector and create small VB 2008 program to connect with Mysql. it is working fine. I have used Host name='localhost'. so... I need to run this program outside of my pc. and out side of my network. I have set my router redirect to mysql installed PC which is my DNS name I have opened Port 3306 from Router also. I need to know what are the settings I have to change and add in to MySql configurations
Can't connect to my database   (378 Views)
I've scanned the posts here, and there's no shortage of queries relating to difficulty connecting to an SQL database. But I still haven't managed it. I've used the Instance configuration wizard to set up an instance, with a root password of "root". I've then used the SQL Command Line Client to log in and create a database called "NigelDB", and created a single table (called NigelFile", which does appear when I type "show tables;". So when my C# program executes the following: string connectionString = "server=localhost; database=NigelDB ;uid=root; pwd=root"; SqlConnection mySqlConnection = new SqlConnection(connectionString); mySqlConnection.Open(); I thought it might establish a connection. However the last of these three statement throws the error: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) I'm pretty sure the instance IS configured to allow remote connections, though God knows why it should want them - I'm working on one machine only! Help!
Preparing text and extracting it from a database   (207 Views)
I'm trying to figure out the best way to prepare text for my database. Right now I'm doing this before inserting it: $title = mysql_real_escape_string($_POST['title']); Then when I extract it to display to the user, I'm doing this: $title = htmlentities(stripslashes($result_set['title'])); Is there a better way to undo the effects of mres Should I use something other than mres
How to implement query to search (tag, id) database?   (258 Views)
So say I have a table full of one word tags, each associated with an ID (foreign key) to something else (product, article, whatever). Each ID can have mupltiple tags, too, such as round, blue, fast... What's the best way to search on a table like this for IDs matching specific search terms The search criteria can me rather complex, too and include NOT. An example would be finding all IDs that are ((blue AND round) AND NOT fast). Hopefully that explains it.
Optimizing my database design   (167 Views)
I am currently managing a social networking website which has a few hundred thousand members, and I am starting to run into some problems with the load created by the search features on the website. Right now, the users' table contains a good deal of data for each user on the site, and we allow people to make searches for users using various criterias. of the criterias (like male or female, age, etc.) have their own columns in that table, with most columns being tinyints or ints. I have added indexes to pretty much every individual column that can be used as a search criteria. The current design is still working, but it is starting to create a big load on the database, and this will only get worse as the site continues to add members. Unfortunately, the fastest way to perform the searches would be to have a single index that covers all the criterias instead of a multitude of indexes being merged together by mysql dynamically. But this solution does not work with how our searches are being done, since every criteria is optional. So if I was to do an index like 'sex,age,height,weight,etc', this index would be useless if someone performed a search that does not involve gender, or a search that does not involve age, etc. After a lot of research, the only alternative solution I came up with was to create a SET column, which would hold a bunch of different data about the user (male, female, tall, average_height, short, big, average_weight, small, etc.) and then run the search query against this single column with the find_in_set function. Problem with this solution is that I am restricting myself to 64 possible informations, which I guess could cover the various search criterias we have available right now, but would not allow for much growth in the future if we want to add new criterias. From the tests I ran with a dummy table filled with random data, this setup would perform the searches much faster than the current setup (and still run fine with even 3 million entries to search through), but I just have a nagging feeling that this is not the best solution available. It also creates the problem of keeping duplicate information in the database and managing this duplicate information when members update their profile, etc. But a 64 bit column on a few hundred thousand records is only a few MBs of disk space, so it is not that big of a deal. Does anyone know of alternative solutions I could use to search efficiently through a big table using a variety of different rows
Link 2 databases   (144 Views)
hey guy i have 2 catalogues on line and one of them my partner made a datasinc to sinc the catalogue information to his access databse(pc) so the stock would always be up to date. now i got him a second catalogue for retail sales and we have the same products. my question is since the both databses or on the same server is it possible to link both 2 keep the stock up to date on the other one
How to get my database connection to work using XAMPP   (249 Views)
hi i have installed XAMPP and its got php and apache working fine but mysql does not work i get the error: Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'localhost' (using password: YES) in C:\xampp\htdocs\testDB.php on line 5 couldn't connect localhost i have not configured anything for the database, because i don'nt know how to using XAMPP how do I even create a database using XAMPP
Where is my database stored?   (211 Views)
days old newbie. I downloaded MySQL 5.1 and I'm up and running. I created a database & some tables & I'm creating some queries & having fun. However, I decided to take a look at where my database is stored because I'm replacing my machine next week & I can't find it I've ran a search & looked thru the folders for MySQL in explorer and I can't find it. It opens OK when I run MySQL 5.1 but where is it hiding Thank you for your help.
Need help with a database design   (208 Views)
I have agents that will record a ticket for each call they take. That ticket will have a varying amount of information depending on the callers actions. I want to be able to store and determine on that call exactly which actions occurred. There are several sections, but one in particular has 7 different options (lets call them a through G). I could make a table and record a 1 or a 0 for each of those seven options. If I did so then I would have a table with 33 or so columns, which would become messier if options needed to be added. So I thought of creating a sort of binary system where A = 1, B = 2, C = 4, D = 8, etc. The sum of all the options in that group would be entered into one column. This easily allows me to add future options to that group without expanding the table, but the total number of combinations would expand rapidly and would make queries difficult (e.g. 7 options equals 128 total combinations - some of them can't occur since D can't happen if E is picked, but still its a large amount). Well, there it would you approach creating a table like this
Replication now replicating all databases   (164 Views)
I recently realized my slave db did not have all the same databases as the master does. I ran the following commands and here is the output: Master Code: mysql> show master status \G *************************** 1. row *************************** File: mysql-bin.000014 Position: 629904494 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) Slave Quote: mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 389373355 Relay_Log_File: us-db1-relay-bin.002553 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 389373355 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_owed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) I see that the Slave status Master Log position does not change yet it says it is zero Seconds behind the master.