I was looking at some auction projects that use a single listings table for all auctions but I know on auction sites the form will be different for different types of items like if you’re selling a cell phone there’ll be a form field for network, carrier, whatever and if you’re selling a book there’ll be form fields for publisher, year of publication, so they would have separate tables I assume for books, cell phones, etc? Then how would they treat them all as one?
Personally, I would do this:
auctions table
(with the basic shared information of all auctions and an “auction type”))
properties table
property_name (like network, carrier, publisher, etc)
auction_type (like cellphone, book, etc)
auctions_properties
auction_id
property_id
value
That way you can have any number of auction types with any number of specific properties with just 3 tables.
Note that the “value” column would be some string variation (VARCHAR, CHAR, TEXT, etc) depending on your needs, maybe you want to redesign it a little if you want to store different types. Like if you want to store an integer (and retrieve an integer) you’ll have to save the original type and reparse it (you could use serialization but that requires a TEXT column and maybe you can’t use that many space)
Agreed - relational tables are the way to go here. Only one suggestion I’d make is around semantics, I’d rename auctions to auction_items, and then the join table would need to be auction_item_properties.
I may be misunderstanding, but my thinking is an auction to be the top-level bucket which has properties like a start time, end time, location, membership, lots/items, etc - so it would have many items.
If each auction is item-specific however, then ignore this comment 
I’m trying to agree with you but why would an auction have many items?
Where do the values of the properties go? I don’t mean the property name
[response inline]
Yes, sorry I see, that’s the value column in auction_properties
But then a single item will contain many records, one for each property, wouldn’t that create too-large-tables?
[response inline]
I can’t make up my mind First, there’s countless types of items Next they may use the category in deciding which form to load We haven’t take categories into account yet in this discussion
Another option (if your database accepts it, like postgres or newer MySQL versions) is to use a column with JSON or JSONB type. You could have a json object with all property/value pairs. I’m not sure about performance, indexing, etc on JSON columns though.
I’m not sure what are the categories you are talking about now, you mean the “auction type”?
You’ll have to have many many millions of auctions in order to make the auction_properties tables too large (if your ID is an int, you have 2,000 MILLION ids to use, if you use bigint I don’t even know that number, you’ll need really good indexes though haha), and even if you ever get near some critical situation you could split table by auction type or something like that. I doubt it’s something you have to worry about right now, you’ll have a lot of more important things to improve before reaching that.
When you sell something on ebay there’s no multiple choice of item type to sell, they figure out the type of item and the category from the title you provide but then ask you to confirm or change the category At this primitive stage I was thinking of adding an intermediate page to the new action to allow the user to select the type or category and then when they submit that page the relevant form will be on the next page There’s such a thing as fallback also so I think I should build a rest framework first, I don’t know
Could I give the join table different value fields for different data types llike text_value, date_value, string_value, …
What data type do you suggest for value column
I have a complete project as like in ruby on rail with same tables name you can contact with me further on my email
Best Regards: MUHAMMAD FURQAN
[response inline]
The easiest but too big is a text columns so you can use activerecord’ serialization of attributes to save anything you want on a text field and activerecord will handle casting.
You could use a string column if you know your values won’t be too big (VARCHAR(255)) and you know what types you want to accept and handle serialization yourself like:
def value=(something)
val = if value.is_a?(Integer)
"integer:#{something}"
elsif value.is_a?(String)
"string:#{something}"
elsif value.is_a?(Date)
"date:#{something}
# etc...
write_attribute(:value, val)
end
def value
type, val = read_attribute(:value).split(':')
case type
when "integer" then val.to_i
when "string" then val
when "date" then Date.parse(val)
etc...
end
If I’m using a string column then whatever value is put in it is a string, so I don’t understand all these “'if value.is_a?” conditions
You were asking a way to store different values, I thought you wanted to preserve the type (so you can distinguish 1 from “1” when you read the record).
If you think strings are enough for your requirements just use a string column, if you want to use one column to store values but preserve the original type you have to serialize the value somehow (that’s the is_a?.. when serializing and the case when casting).
Another option is to use two columns: one for the stringified value and one for the original type so you can parse that again.
whats your email
