Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
Having known nothing about database, we have a friend building an online purchase order system, using MySQL.
We have various tables and rows, relating to the items, costs, discount, description etc etc
In the backend, we also have a list of all the items, addresses, part numbers etc
The problem we face at the moment, is parts are duplicated, spelt wrong etc etc.
Logically, should it be possible to remove an item/part from the DB but make it still available to the previous orders?
I.E if we order a left handed widget last week, but this item has since been discontinued, we would want to remove it from our DB. Can this be done without affecting the previous order and it being removed?
They appear to have done almost this, in as much that adding a part to a new order, we can manipulate the price if its changed, but that doesnt affect previous orders.
I know nothing about databases, so any help in simple terms is appreciated,
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
Add an extra column to the table in the database which stores the item called 'visibility' (or similar), give it a boolean data type and adjust your software so that you can set the products visibility to true or false. The information will still be there in the database, but you can then choose whether it's visible by adding 'WHERE visibility==true' or 'WHERE visibility==false' to the database queries.
[Edited on 23-04-2012 by ed]
|
Nismo
Member
Registered: 12th Sep 02
User status: Offline
|
I would add a row to the table called 'status' or something, then this row could be populated for each item like:
'status'
Current
Discontinued
out of stock
back order
this way the item stays in the db but is not shown if disabled or something?
and if you want to do it properly , create a new table with rows:
'ID' 'Status'
1 Current
2 Discontinued
3 Out of stock
4 Back order
then on your main stock table you add a row for 'status_id' and reference the ID number.
I.E
ID Part Status
01 widget 1
02 flop 3
03 jam 2
|
Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
quote:
Originally posted by ed
Add an extra column to the table in the database which stores the item called 'visibility' (or similar), give it a boolean data type and adjust your software so that you can set the products visibility to true or false. The information will still be there in the database, but you can then choose whether it's visible by adding 'WHERE visibility==true' or 'WHERE visibility==false' to the database queries.
[Edited on 23-04-2012 by ed]
So if we delete the item, it gets set to false.
Would this cause any problems if someone added the item again with the same part number etc?
Just trying to think ahead.
Ultimatly, we do want rid of the duplicated items, rather than the items have a status 'visible, out of stock, discontinued etc.
The biggest issue we have is people have named parts very similar and its messy, we want rid of them from the database, typical things are
Part | description:
123-456-798 | left handed widget
123 456-789 | left handed widget
123.456.789 | left handed widget
[Edited on 23-04-2012 by Bart]
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
quote:
Originally posted by Bart
quote:
Originally posted by ed
Add an extra column to the table in the database which stores the item called 'visibility' (or similar), give it a boolean data type and adjust your software so that you can set the products visibility to true or false. The information will still be there in the database, but you can then choose whether it's visible by adding 'WHERE visibility==true' or 'WHERE visibility==false' to the database queries.
[Edited on 23-04-2012 by ed]
So if we delete the item, it gets set to false.
Would this cause any problems if someone added the item again with the same part number etc?
Just trying to think ahead.
Ultimatly, we do want rid of the duplicated items, rather than the items have a status 'visible, out of stock, discontinued etc.
The biggest issue we have is people have named parts very similar and its messy, we want rid of them from the database, typical things are
Part | description:
123-456-798 | left handed widget
123 456-789 | left handed widget
123.456.789 | left handed widget
[Edited on 23-04-2012 by Bart]
You should make the part number a unique identifier in the database and then make sure there is validation on anywhere that part numbers get entered so they are consistent.
For example, make it so that part numbers have to be xxx-xxx-xxx and if they enter xxx.xxx.xxx, it stops them saving it.
|
noshua
Member
Registered: 19th Nov 08
User status: Offline
|
Do as ed or Nismo said, then the page that displays all your products, edit the query to only show ones that are in stock (i.e. not discontinued).
Instead of deleting the item you only need to update it's 'status' to discontinued.
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
Also, if those are your biggest issues then you could write some software that would go through and bring up and correct similar records. But as James said, the part number should be unique and the key, that way you can make a proper relational database as Nismo explained.
|
noshua
Member
Registered: 19th Nov 08
User status: Offline
|
Oh and use an input mask script to allow a specific entry of data;
http://www.javascriptsource.com/forms/dfilter.html
First one I found, could do it much easier with jQuery but you might as well use the one above unless you already have jQuery on your website
|
Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
thanks guys.
I think i'll set a visibility status to "Show / Hide".
Default product database will only display Show items, but will make a link available to hidden items.
We cant force all part numbers to be the same, bare in mind this is parts we're ordering from hundreds if not thousands of suppliers, everyone has different part numbers and ways of being entered.
|
noshua
Member
Registered: 19th Nov 08
User status: Offline
|
If each supplier has a specific way they present the data you could put a drop down menu or something similar with a list of manufacturers, then the input mask changes based on the manufacturer selected.
|
James
Member
Registered: 1st Jun 02
Location: Surrey
User status: Offline
|
quote:
Originally posted by Bart
We cant force all part numbers to be the same, bare in mind this is parts we're ordering from hundreds if not thousands of suppliers, everyone has different part numbers and ways of being entered.
If that's the case, you shouldn't use it as a unique identifier. Mainly because there's a (slim) chance that 2 suppliers will use the same part number. You should consider creating a unique identifier internal to your system, call it something like PartID and make it auto increment each time a new part is added to the database.
|
ed
Member
Registered: 10th Sep 03
User status: Offline
|
Presumably you store who the supplier is too - perhaps you could make some supplier dependant validation rules and input masks.
|
Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
quote:
Originally posted by ed
Presumably you store who the supplier is too - perhaps you could make some supplier dependant validation rules and input masks.
Yes, and infact we do have the same part number in more than once under different suppliers, since we'll use another supplier if we're on stop or its unavailable from another supplier.
|
