I am building a mini site that will allow people to search for gadgets (phones, tablets etc..) by name and model number, the issue I have is how best to setup the database when you take into account the name conventions of some phones.
For instance, the Samsung Galaxy S mobile phone has numerous variations as well as the model number which is GT-I9000. e.g Samsung Galaxy S GT-I9000 (Variations below)
Would anybody be able to offer some advice on the best way for me to design these tables - essentially this project will have quite a large database eventually so I am trying to plan it accordingly.
I am hoping to be able to setup the database to enable the user to search for 'Captivate' or 'Vibrant' and that would show them the Galaxy S GT-I9000 as this is the 'main' name for the device.
I have currently have the following database tables
brands - id, brand (eg 2, Samsung)
devices - id, brand_id, device_type_id, name (eg 1, 2, 3, Galaxy S)
device_types - id, type (eg 3, phone)
Any advice would be greatly appreciated. Let me know if more information is required to understand what I am trying to achieve, i'll be using MySQL for the database.
Litigation support systems run into this problem all the time with respect to people. The usual approach is to build a table of aliases. The simplest structure is a table of just two columns.
create table device_aliases (
device_id integer not null,
device_alias varchar(20) not null,
primary key (device_id, device_alias),
foreign key (device_id) references devices (id)
);