Please accept my apology if I'm imposing... This is a lengthy post. I'm attempting my first database with a new web site and I'm SCARED that I'm setting up the tables incorrectly as I'm still learning and don't know if my queries and searches will be hampered by my ineptness down the road.
I would appreciate anyone that could take the time to review what I've got and maybe troubleshoot it for me before I create myself a monster. ;-)
The idea is to put all the items people want to sell on the site into a database (MySQL) and use PHP to design the results page showing the following:
1. item #
2. description (long text)
3. price
4. location
5. brand
6. if it is a single item
7. if it is complete set
8. contact (which is either InHomeOutlet.com, or they can offer their choice of direct contact info)
9. if it can be purchased with a credit card
The tables are set up as follows:
Table 1= CUSTOMER Containing the following COLUMNS:
1. customer name
2. customer #
3. street address
4. city
5. state
6. zip
7. country
8. phone
9. email
10. advertiser (null or not null)
11. lister (null or not null)
Table 2= ITEM Containing the following COLUMNS:
1. item ID#
2. customer #
3. image (blog field with link to image)
4. price
Table 3= DESCRIPTION Containing the following COLUMNS:
1. item ID#
2. long text
3. single item (null or not null)
4. part of set (null or not null)
5. brand
6. location
7. credit card (null or not null)
Table 4= CONTACT Contains the following COLUMNS:
1. item ID#
2. inhomeoutlet.com (null or not null, if null, then the following fields would be filled in according to what they choose)
3. name
4. street address
5. city
6. state
7. zip
8. day phone
9. evening phone
10. email
These next three tables I'm not exactly sure how to set up...
Table 5= STATES (note: Some items are offered in multiple states)
I thought it best to make each COLUMN a STATE, then I'm not sure if it's best to have an additional COLUMN for 'item ID#' and then enter 'null' or 'not null' for each state...
OR
Have each each COLUMN a STATE, then enter the 'item ID#' for the STATES that would apply.
Table 6= ROOMS (note: some items are listed in multiple rooms)
There are 12 rooms, and each COLUMN would be a room. I have the same dilemma as I do for the STATES table, whether to provide a COLUMN for the item ID# with null or not null for each room, or insert the item ID# under the appropriate ROOMS.
lastly...
Table 6= ITEM TYPES (note: there are 48 types at this time and items can be considered multiple types)
Same question with this one, do I insert the item ID# under the appropriate TYPES or do I provide a COLUMN for the item ID#, and enter 'null' or 'not null' for each TYPE?
This might all make more sense if you surf around my site a bit and see what we are doing there. I've been surfing like mad all the tutorials and trying to gather the information on my own, and I'm confident I can program the tables and everything, I'm just not sure what is the most efficient way to set it up. Thank you sooooo much!!
Carrie
I would appreciate anyone that could take the time to review what I've got and maybe troubleshoot it for me before I create myself a monster. ;-)
The idea is to put all the items people want to sell on the site into a database (MySQL) and use PHP to design the results page showing the following:
1. item #
2. description (long text)
3. price
4. location
5. brand
6. if it is a single item
7. if it is complete set
8. contact (which is either InHomeOutlet.com, or they can offer their choice of direct contact info)
9. if it can be purchased with a credit card
The tables are set up as follows:
Table 1= CUSTOMER Containing the following COLUMNS:
1. customer name
2. customer #
3. street address
4. city
5. state
6. zip
7. country
8. phone
9. email
10. advertiser (null or not null)
11. lister (null or not null)
Table 2= ITEM Containing the following COLUMNS:
1. item ID#
2. customer #
3. image (blog field with link to image)
4. price
Table 3= DESCRIPTION Containing the following COLUMNS:
1. item ID#
2. long text
3. single item (null or not null)
4. part of set (null or not null)
5. brand
6. location
7. credit card (null or not null)
Table 4= CONTACT Contains the following COLUMNS:
1. item ID#
2. inhomeoutlet.com (null or not null, if null, then the following fields would be filled in according to what they choose)
3. name
4. street address
5. city
6. state
7. zip
8. day phone
9. evening phone
10. email
These next three tables I'm not exactly sure how to set up...
Table 5= STATES (note: Some items are offered in multiple states)
I thought it best to make each COLUMN a STATE, then I'm not sure if it's best to have an additional COLUMN for 'item ID#' and then enter 'null' or 'not null' for each state...
OR
Have each each COLUMN a STATE, then enter the 'item ID#' for the STATES that would apply.
Table 6= ROOMS (note: some items are listed in multiple rooms)
There are 12 rooms, and each COLUMN would be a room. I have the same dilemma as I do for the STATES table, whether to provide a COLUMN for the item ID# with null or not null for each room, or insert the item ID# under the appropriate ROOMS.
lastly...
Table 6= ITEM TYPES (note: there are 48 types at this time and items can be considered multiple types)
Same question with this one, do I insert the item ID# under the appropriate TYPES or do I provide a COLUMN for the item ID#, and enter 'null' or 'not null' for each TYPE?
This might all make more sense if you surf around my site a bit and see what we are doing there. I've been surfing like mad all the tutorials and trying to gather the information on my own, and I'm confident I can program the tables and everything, I'm just not sure what is the most efficient way to set it up. Thank you sooooo much!!
Carrie